1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot._typing import E
  27from sqlglot.errors import ErrorLevel, ParseError
  28from sqlglot.helper import (
  29    AutoName,
  30    camel_to_snake_case,
  31    ensure_collection,
  32    ensure_list,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot.dialects.dialect import DialectType
  40
  41
  42class _Expression(type):
  43    def __new__(cls, clsname, bases, attrs):
  44        klass = super().__new__(cls, clsname, bases, attrs)
  45
  46        # When an Expression class is created, its key is automatically set to be
  47        # the lowercase version of the class' name.
  48        klass.key = clsname.lower()
  49
  50        # This is so that docstrings are not inherited in pdoc
  51        klass.__doc__ = klass.__doc__ or ""
  52
  53        return klass
  54
  55
  56SQLGLOT_META = "sqlglot.meta"
  57TABLE_PARTS = ("this", "db", "catalog")
  58
  59
  60class Expression(metaclass=_Expression):
  61    """
  62    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  63    context, such as its child expressions, their names (arg keys), and whether a given child expression
  64    is optional or not.
  65
  66    Attributes:
  67        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  68            and representing expressions as strings.
  69        arg_types: determines what arguments (child nodes) are supported by an expression. It
  70            maps arg keys to booleans that indicate whether the corresponding args are optional.
  71        parent: a reference to the parent expression (or None, in case of root expressions).
  72        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  73            uses to refer to it.
  74        comments: a list of comments that are associated with a given expression. This is used in
  75            order to preserve comments when transpiling SQL code.
  76        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  77            optimizer, in order to enable some transformations that require type information.
  78        meta: a dictionary that can be used to store useful metadata for a given expression.
  79
  80    Example:
  81        >>> class Foo(Expression):
  82        ...     arg_types = {"this": True, "expression": False}
  83
  84        The above definition informs us that Foo is an Expression that requires an argument called
  85        "this" and may also optionally receive an argument called "expression".
  86
  87    Args:
  88        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  89    """
  90
  91    key = "expression"
  92    arg_types = {"this": True}
  93    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  94
  95    def __init__(self, **args: t.Any):
  96        self.args: t.Dict[str, t.Any] = args
  97        self.parent: t.Optional[Expression] = None
  98        self.arg_key: t.Optional[str] = None
  99        self.comments: t.Optional[t.List[str]] = None
 100        self._type: t.Optional[DataType] = None
 101        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 102        self._hash: t.Optional[int] = None
 103
 104        for arg_key, value in self.args.items():
 105            self._set_parent(arg_key, value)
 106
 107    def __eq__(self, other) -> bool:
 108        return type(self) is type(other) and hash(self) == hash(other)
 109
 110    @property
 111    def hashable_args(self) -> t.Any:
 112        return frozenset(
 113            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 114            for k, v in self.args.items()
 115            if not (v is None or v is False or (type(v) is list and not v))
 116        )
 117
 118    def __hash__(self) -> int:
 119        if self._hash is not None:
 120            return self._hash
 121
 122        return hash((self.__class__, self.hashable_args))
 123
 124    @property
 125    def this(self) -> t.Any:
 126        """
 127        Retrieves the argument with key "this".
 128        """
 129        return self.args.get("this")
 130
 131    @property
 132    def expression(self) -> t.Any:
 133        """
 134        Retrieves the argument with key "expression".
 135        """
 136        return self.args.get("expression")
 137
 138    @property
 139    def expressions(self) -> t.List[t.Any]:
 140        """
 141        Retrieves the argument with key "expressions".
 142        """
 143        return self.args.get("expressions") or []
 144
 145    def text(self, key) -> str:
 146        """
 147        Returns a textual representation of the argument corresponding to "key". This can only be used
 148        for args that are strings or leaf Expression instances, such as identifiers and literals.
 149        """
 150        field = self.args.get(key)
 151        if isinstance(field, str):
 152            return field
 153        if isinstance(field, (Identifier, Literal, Var)):
 154            return field.this
 155        if isinstance(field, (Star, Null)):
 156            return field.name
 157        return ""
 158
 159    @property
 160    def is_string(self) -> bool:
 161        """
 162        Checks whether a Literal expression is a string.
 163        """
 164        return isinstance(self, Literal) and self.args["is_string"]
 165
 166    @property
 167    def is_number(self) -> bool:
 168        """
 169        Checks whether a Literal expression is a number.
 170        """
 171        return isinstance(self, Literal) and not self.args["is_string"]
 172
 173    @property
 174    def is_int(self) -> bool:
 175        """
 176        Checks whether a Literal expression is an integer.
 177        """
 178        if self.is_number:
 179            try:
 180                int(self.name)
 181                return True
 182            except ValueError:
 183                pass
 184        return False
 185
 186    @property
 187    def is_star(self) -> bool:
 188        """Checks whether an expression is a star."""
 189        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 190
 191    @property
 192    def alias(self) -> str:
 193        """
 194        Returns the alias of the expression, or an empty string if it's not aliased.
 195        """
 196        if isinstance(self.args.get("alias"), TableAlias):
 197            return self.args["alias"].name
 198        return self.text("alias")
 199
 200    @property
 201    def alias_column_names(self) -> t.List[str]:
 202        table_alias = self.args.get("alias")
 203        if not table_alias:
 204            return []
 205        return [c.name for c in table_alias.args.get("columns") or []]
 206
 207    @property
 208    def name(self) -> str:
 209        return self.text("this")
 210
 211    @property
 212    def alias_or_name(self) -> str:
 213        return self.alias or self.name
 214
 215    @property
 216    def output_name(self) -> str:
 217        """
 218        Name of the output column if this expression is a selection.
 219
 220        If the Expression has no output name, an empty string is returned.
 221
 222        Example:
 223            >>> from sqlglot import parse_one
 224            >>> parse_one("SELECT a").expressions[0].output_name
 225            'a'
 226            >>> parse_one("SELECT b AS c").expressions[0].output_name
 227            'c'
 228            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 229            ''
 230        """
 231        return ""
 232
 233    @property
 234    def type(self) -> t.Optional[DataType]:
 235        return self._type
 236
 237    @type.setter
 238    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 239        if dtype and not isinstance(dtype, DataType):
 240            dtype = DataType.build(dtype)
 241        self._type = dtype  # type: ignore
 242
 243    def is_type(self, *dtypes) -> bool:
 244        return self.type is not None and self.type.is_type(*dtypes)
 245
 246    def is_leaf(self) -> bool:
 247        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 248
 249    @property
 250    def meta(self) -> t.Dict[str, t.Any]:
 251        if self._meta is None:
 252            self._meta = {}
 253        return self._meta
 254
 255    def __deepcopy__(self, memo):
 256        copy = self.__class__(**deepcopy(self.args))
 257        if self.comments is not None:
 258            copy.comments = deepcopy(self.comments)
 259
 260        if self._type is not None:
 261            copy._type = self._type.copy()
 262
 263        if self._meta is not None:
 264            copy._meta = deepcopy(self._meta)
 265
 266        return copy
 267
 268    def copy(self):
 269        """
 270        Returns a deep copy of the expression.
 271        """
 272        new = deepcopy(self)
 273        new.parent = self.parent
 274        return new
 275
 276    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 277        if self.comments is None:
 278            self.comments = []
 279        if comments:
 280            for comment in comments:
 281                _, *meta = comment.split(SQLGLOT_META)
 282                if meta:
 283                    for kv in "".join(meta).split(","):
 284                        k, *v = kv.split("=")
 285                        value = v[0].strip() if v else True
 286                        self.meta[k.strip()] = value
 287                self.comments.append(comment)
 288
 289    def append(self, arg_key: str, value: t.Any) -> None:
 290        """
 291        Appends value to arg_key if it's a list or sets it as a new list.
 292
 293        Args:
 294            arg_key (str): name of the list expression arg
 295            value (Any): value to append to the list
 296        """
 297        if not isinstance(self.args.get(arg_key), list):
 298            self.args[arg_key] = []
 299        self.args[arg_key].append(value)
 300        self._set_parent(arg_key, value)
 301
 302    def set(self, arg_key: str, value: t.Any) -> None:
 303        """
 304        Sets arg_key to value.
 305
 306        Args:
 307            arg_key: name of the expression arg.
 308            value: value to set the arg to.
 309        """
 310        if value is None:
 311            self.args.pop(arg_key, None)
 312            return
 313
 314        self.args[arg_key] = value
 315        self._set_parent(arg_key, value)
 316
 317    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 318        if hasattr(value, "parent"):
 319            value.parent = self
 320            value.arg_key = arg_key
 321        elif type(value) is list:
 322            for v in value:
 323                if hasattr(v, "parent"):
 324                    v.parent = self
 325                    v.arg_key = arg_key
 326
 327    @property
 328    def depth(self) -> int:
 329        """
 330        Returns the depth of this tree.
 331        """
 332        if self.parent:
 333            return self.parent.depth + 1
 334        return 0
 335
 336    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 337        """Yields the key and expression for all arguments, exploding list args."""
 338        for k, vs in self.args.items():
 339            if type(vs) is list:
 340                for v in vs:
 341                    if hasattr(v, "parent"):
 342                        yield k, v
 343            else:
 344                if hasattr(vs, "parent"):
 345                    yield k, vs
 346
 347    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 348        """
 349        Returns the first node in this tree which matches at least one of
 350        the specified types.
 351
 352        Args:
 353            expression_types: the expression type(s) to match.
 354            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 355
 356        Returns:
 357            The node which matches the criteria or None if no such node was found.
 358        """
 359        return next(self.find_all(*expression_types, bfs=bfs), None)
 360
 361    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 362        """
 363        Returns a generator object which visits all nodes in this tree and only
 364        yields those that match at least one of the specified expression types.
 365
 366        Args:
 367            expression_types: the expression type(s) to match.
 368            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 369
 370        Returns:
 371            The generator object.
 372        """
 373        for expression, *_ in self.walk(bfs=bfs):
 374            if isinstance(expression, expression_types):
 375                yield expression
 376
 377    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 378        """
 379        Returns a nearest parent matching expression_types.
 380
 381        Args:
 382            expression_types: the expression type(s) to match.
 383
 384        Returns:
 385            The parent node.
 386        """
 387        ancestor = self.parent
 388        while ancestor and not isinstance(ancestor, expression_types):
 389            ancestor = ancestor.parent
 390        return t.cast(E, ancestor)
 391
 392    @property
 393    def parent_select(self) -> t.Optional[Select]:
 394        """
 395        Returns the parent select statement.
 396        """
 397        return self.find_ancestor(Select)
 398
 399    @property
 400    def same_parent(self) -> bool:
 401        """Returns if the parent is the same class as itself."""
 402        return type(self.parent) is self.__class__
 403
 404    def root(self) -> Expression:
 405        """
 406        Returns the root expression of this tree.
 407        """
 408        expression = self
 409        while expression.parent:
 410            expression = expression.parent
 411        return expression
 412
 413    def walk(self, bfs=True, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree.
 416
 417        Args:
 418            bfs (bool): if set to True the BFS traversal order will be applied,
 419                otherwise the DFS traversal will be used instead.
 420            prune ((node, parent, arg_key) -> bool): callable that returns True if
 421                the generator should stop traversing this branch of the tree.
 422
 423        Returns:
 424            the generator object.
 425        """
 426        if bfs:
 427            yield from self.bfs(prune=prune)
 428        else:
 429            yield from self.dfs(prune=prune)
 430
 431    def dfs(self, parent=None, key=None, prune=None):
 432        """
 433        Returns a generator object which visits all nodes in this tree in
 434        the DFS (Depth-first) order.
 435
 436        Returns:
 437            The generator object.
 438        """
 439        parent = parent or self.parent
 440        yield self, parent, key
 441        if prune and prune(self, parent, key):
 442            return
 443
 444        for k, v in self.iter_expressions():
 445            yield from v.dfs(self, k, prune)
 446
 447    def bfs(self, prune=None):
 448        """
 449        Returns a generator object which visits all nodes in this tree in
 450        the BFS (Breadth-first) order.
 451
 452        Returns:
 453            The generator object.
 454        """
 455        queue = deque([(self, self.parent, None)])
 456
 457        while queue:
 458            item, parent, key = queue.popleft()
 459
 460            yield item, parent, key
 461            if prune and prune(item, parent, key):
 462                continue
 463
 464            for k, v in item.iter_expressions():
 465                queue.append((v, item, k))
 466
 467    def unnest(self):
 468        """
 469        Returns the first non parenthesis child or self.
 470        """
 471        expression = self
 472        while type(expression) is Paren:
 473            expression = expression.this
 474        return expression
 475
 476    def unalias(self):
 477        """
 478        Returns the inner expression if this is an Alias.
 479        """
 480        if isinstance(self, Alias):
 481            return self.this
 482        return self
 483
 484    def unnest_operands(self):
 485        """
 486        Returns unnested operands as a tuple.
 487        """
 488        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 489
 490    def flatten(self, unnest=True):
 491        """
 492        Returns a generator which yields child nodes whose parents are the same class.
 493
 494        A AND B AND C -> [A, B, C]
 495        """
 496        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 497            if not type(node) is self.__class__:
 498                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 499
 500    def __str__(self) -> str:
 501        return self.sql()
 502
 503    def __repr__(self) -> str:
 504        return _to_s(self)
 505
 506    def to_s(self) -> str:
 507        """
 508        Same as __repr__, but includes additional information which can be useful
 509        for debugging, like empty or missing args and the AST nodes' object IDs.
 510        """
 511        return _to_s(self, verbose=True)
 512
 513    def sql(self, dialect: DialectType = None, **opts) -> str:
 514        """
 515        Returns SQL string representation of this tree.
 516
 517        Args:
 518            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 519            opts: other `sqlglot.generator.Generator` options.
 520
 521        Returns:
 522            The SQL string.
 523        """
 524        from sqlglot.dialects import Dialect
 525
 526        return Dialect.get_or_raise(dialect).generate(self, **opts)
 527
 528    def transform(self, fun, *args, copy=True, **kwargs):
 529        """
 530        Recursively visits all tree nodes (excluding already transformed ones)
 531        and applies the given transformation function to each node.
 532
 533        Args:
 534            fun (function): a function which takes a node as an argument and returns a
 535                new transformed node or the same node without modifications. If the function
 536                returns None, then the corresponding node will be removed from the syntax tree.
 537            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 538                modified in place.
 539
 540        Returns:
 541            The transformed tree.
 542        """
 543        node = self.copy() if copy else self
 544        new_node = fun(node, *args, **kwargs)
 545
 546        if new_node is None or not isinstance(new_node, Expression):
 547            return new_node
 548        if new_node is not node:
 549            new_node.parent = node.parent
 550            return new_node
 551
 552        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 553        return new_node
 554
 555    @t.overload
 556    def replace(self, expression: E) -> E:
 557        ...
 558
 559    @t.overload
 560    def replace(self, expression: None) -> None:
 561        ...
 562
 563    def replace(self, expression):
 564        """
 565        Swap out this expression with a new expression.
 566
 567        For example::
 568
 569            >>> tree = Select().select("x").from_("tbl")
 570            >>> tree.find(Column).replace(column("y"))
 571            Column(
 572              this=Identifier(this=y, quoted=False))
 573            >>> tree.sql()
 574            'SELECT y FROM tbl'
 575
 576        Args:
 577            expression: new node
 578
 579        Returns:
 580            The new expression or expressions.
 581        """
 582        if not self.parent:
 583            return expression
 584
 585        parent = self.parent
 586        self.parent = None
 587
 588        replace_children(parent, lambda child: expression if child is self else child)
 589        return expression
 590
 591    def pop(self: E) -> E:
 592        """
 593        Remove this expression from its AST.
 594
 595        Returns:
 596            The popped expression.
 597        """
 598        self.replace(None)
 599        return self
 600
 601    def assert_is(self, type_: t.Type[E]) -> E:
 602        """
 603        Assert that this `Expression` is an instance of `type_`.
 604
 605        If it is NOT an instance of `type_`, this raises an assertion error.
 606        Otherwise, this returns this expression.
 607
 608        Examples:
 609            This is useful for type security in chained expressions:
 610
 611            >>> import sqlglot
 612            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 613            'SELECT x, z FROM y'
 614        """
 615        assert isinstance(self, type_)
 616        return self
 617
 618    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 619        """
 620        Checks if this expression is valid (e.g. all mandatory args are set).
 621
 622        Args:
 623            args: a sequence of values that were used to instantiate a Func expression. This is used
 624                to check that the provided arguments don't exceed the function argument limit.
 625
 626        Returns:
 627            A list of error messages for all possible errors that were found.
 628        """
 629        errors: t.List[str] = []
 630
 631        for k in self.args:
 632            if k not in self.arg_types:
 633                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 634        for k, mandatory in self.arg_types.items():
 635            v = self.args.get(k)
 636            if mandatory and (v is None or (isinstance(v, list) and not v)):
 637                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 638
 639        if (
 640            args
 641            and isinstance(self, Func)
 642            and len(args) > len(self.arg_types)
 643            and not self.is_var_len_args
 644        ):
 645            errors.append(
 646                f"The number of provided arguments ({len(args)}) is greater than "
 647                f"the maximum number of supported arguments ({len(self.arg_types)})"
 648            )
 649
 650        return errors
 651
 652    def dump(self):
 653        """
 654        Dump this Expression to a JSON-serializable dict.
 655        """
 656        from sqlglot.serde import dump
 657
 658        return dump(self)
 659
 660    @classmethod
 661    def load(cls, obj):
 662        """
 663        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 664        """
 665        from sqlglot.serde import load
 666
 667        return load(obj)
 668
 669    def and_(
 670        self,
 671        *expressions: t.Optional[ExpOrStr],
 672        dialect: DialectType = None,
 673        copy: bool = True,
 674        **opts,
 675    ) -> Condition:
 676        """
 677        AND this condition with one or multiple expressions.
 678
 679        Example:
 680            >>> condition("x=1").and_("y=1").sql()
 681            'x = 1 AND y = 1'
 682
 683        Args:
 684            *expressions: the SQL code strings to parse.
 685                If an `Expression` instance is passed, it will be used as-is.
 686            dialect: the dialect used to parse the input expression.
 687            copy: whether or not to copy the involved expressions (only applies to Expressions).
 688            opts: other options to use to parse the input expressions.
 689
 690        Returns:
 691            The new And condition.
 692        """
 693        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 694
 695    def or_(
 696        self,
 697        *expressions: t.Optional[ExpOrStr],
 698        dialect: DialectType = None,
 699        copy: bool = True,
 700        **opts,
 701    ) -> Condition:
 702        """
 703        OR this condition with one or multiple expressions.
 704
 705        Example:
 706            >>> condition("x=1").or_("y=1").sql()
 707            'x = 1 OR y = 1'
 708
 709        Args:
 710            *expressions: the SQL code strings to parse.
 711                If an `Expression` instance is passed, it will be used as-is.
 712            dialect: the dialect used to parse the input expression.
 713            copy: whether or not to copy the involved expressions (only applies to Expressions).
 714            opts: other options to use to parse the input expressions.
 715
 716        Returns:
 717            The new Or condition.
 718        """
 719        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 720
 721    def not_(self, copy: bool = True):
 722        """
 723        Wrap this condition with NOT.
 724
 725        Example:
 726            >>> condition("x=1").not_().sql()
 727            'NOT x = 1'
 728
 729        Args:
 730            copy: whether or not to copy this object.
 731
 732        Returns:
 733            The new Not instance.
 734        """
 735        return not_(self, copy=copy)
 736
 737    def as_(
 738        self,
 739        alias: str | Identifier,
 740        quoted: t.Optional[bool] = None,
 741        dialect: DialectType = None,
 742        copy: bool = True,
 743        **opts,
 744    ) -> Alias:
 745        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 746
 747    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 748        this = self.copy()
 749        other = convert(other, copy=True)
 750        if not isinstance(this, klass) and not isinstance(other, klass):
 751            this = _wrap(this, Binary)
 752            other = _wrap(other, Binary)
 753        if reverse:
 754            return klass(this=other, expression=this)
 755        return klass(this=this, expression=other)
 756
 757    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 758        return Bracket(
 759            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 760        )
 761
 762    def __iter__(self) -> t.Iterator:
 763        if "expressions" in self.arg_types:
 764            return iter(self.args.get("expressions") or [])
 765        # We define this because __getitem__ converts Expression into an iterable, which is
 766        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 767        # See: https://peps.python.org/pep-0234/
 768        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 769
 770    def isin(
 771        self,
 772        *expressions: t.Any,
 773        query: t.Optional[ExpOrStr] = None,
 774        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 775        copy: bool = True,
 776        **opts,
 777    ) -> In:
 778        return In(
 779            this=maybe_copy(self, copy),
 780            expressions=[convert(e, copy=copy) for e in expressions],
 781            query=maybe_parse(query, copy=copy, **opts) if query else None,
 782            unnest=Unnest(
 783                expressions=[
 784                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 785                ]
 786            )
 787            if unnest
 788            else None,
 789        )
 790
 791    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 792        return Between(
 793            this=maybe_copy(self, copy),
 794            low=convert(low, copy=copy, **opts),
 795            high=convert(high, copy=copy, **opts),
 796        )
 797
 798    def is_(self, other: ExpOrStr) -> Is:
 799        return self._binop(Is, other)
 800
 801    def like(self, other: ExpOrStr) -> Like:
 802        return self._binop(Like, other)
 803
 804    def ilike(self, other: ExpOrStr) -> ILike:
 805        return self._binop(ILike, other)
 806
 807    def eq(self, other: t.Any) -> EQ:
 808        return self._binop(EQ, other)
 809
 810    def neq(self, other: t.Any) -> NEQ:
 811        return self._binop(NEQ, other)
 812
 813    def rlike(self, other: ExpOrStr) -> RegexpLike:
 814        return self._binop(RegexpLike, other)
 815
 816    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 817        div = self._binop(Div, other)
 818        div.args["typed"] = typed
 819        div.args["safe"] = safe
 820        return div
 821
 822    def __lt__(self, other: t.Any) -> LT:
 823        return self._binop(LT, other)
 824
 825    def __le__(self, other: t.Any) -> LTE:
 826        return self._binop(LTE, other)
 827
 828    def __gt__(self, other: t.Any) -> GT:
 829        return self._binop(GT, other)
 830
 831    def __ge__(self, other: t.Any) -> GTE:
 832        return self._binop(GTE, other)
 833
 834    def __add__(self, other: t.Any) -> Add:
 835        return self._binop(Add, other)
 836
 837    def __radd__(self, other: t.Any) -> Add:
 838        return self._binop(Add, other, reverse=True)
 839
 840    def __sub__(self, other: t.Any) -> Sub:
 841        return self._binop(Sub, other)
 842
 843    def __rsub__(self, other: t.Any) -> Sub:
 844        return self._binop(Sub, other, reverse=True)
 845
 846    def __mul__(self, other: t.Any) -> Mul:
 847        return self._binop(Mul, other)
 848
 849    def __rmul__(self, other: t.Any) -> Mul:
 850        return self._binop(Mul, other, reverse=True)
 851
 852    def __truediv__(self, other: t.Any) -> Div:
 853        return self._binop(Div, other)
 854
 855    def __rtruediv__(self, other: t.Any) -> Div:
 856        return self._binop(Div, other, reverse=True)
 857
 858    def __floordiv__(self, other: t.Any) -> IntDiv:
 859        return self._binop(IntDiv, other)
 860
 861    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 862        return self._binop(IntDiv, other, reverse=True)
 863
 864    def __mod__(self, other: t.Any) -> Mod:
 865        return self._binop(Mod, other)
 866
 867    def __rmod__(self, other: t.Any) -> Mod:
 868        return self._binop(Mod, other, reverse=True)
 869
 870    def __pow__(self, other: t.Any) -> Pow:
 871        return self._binop(Pow, other)
 872
 873    def __rpow__(self, other: t.Any) -> Pow:
 874        return self._binop(Pow, other, reverse=True)
 875
 876    def __and__(self, other: t.Any) -> And:
 877        return self._binop(And, other)
 878
 879    def __rand__(self, other: t.Any) -> And:
 880        return self._binop(And, other, reverse=True)
 881
 882    def __or__(self, other: t.Any) -> Or:
 883        return self._binop(Or, other)
 884
 885    def __ror__(self, other: t.Any) -> Or:
 886        return self._binop(Or, other, reverse=True)
 887
 888    def __neg__(self) -> Neg:
 889        return Neg(this=_wrap(self.copy(), Binary))
 890
 891    def __invert__(self) -> Not:
 892        return not_(self.copy())
 893
 894
 895IntoType = t.Union[
 896    str,
 897    t.Type[Expression],
 898    t.Collection[t.Union[str, t.Type[Expression]]],
 899]
 900ExpOrStr = t.Union[str, Expression]
 901
 902
 903class Condition(Expression):
 904    """Logical conditions like x AND y, or simply x"""
 905
 906
 907class Predicate(Condition):
 908    """Relationships like x = y, x > 1, x >= y."""
 909
 910
 911class DerivedTable(Expression):
 912    @property
 913    def selects(self) -> t.List[Expression]:
 914        return self.this.selects if isinstance(self.this, Subqueryable) else []
 915
 916    @property
 917    def named_selects(self) -> t.List[str]:
 918        return [select.output_name for select in self.selects]
 919
 920
 921class Unionable(Expression):
 922    def union(
 923        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 924    ) -> Unionable:
 925        """
 926        Builds a UNION expression.
 927
 928        Example:
 929            >>> import sqlglot
 930            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 931            'SELECT * FROM foo UNION SELECT * FROM bla'
 932
 933        Args:
 934            expression: the SQL code string.
 935                If an `Expression` instance is passed, it will be used as-is.
 936            distinct: set the DISTINCT flag if and only if this is true.
 937            dialect: the dialect used to parse the input expression.
 938            opts: other options to use to parse the input expressions.
 939
 940        Returns:
 941            The new Union expression.
 942        """
 943        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 944
 945    def intersect(
 946        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 947    ) -> Unionable:
 948        """
 949        Builds an INTERSECT expression.
 950
 951        Example:
 952            >>> import sqlglot
 953            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 954            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 955
 956        Args:
 957            expression: the SQL code string.
 958                If an `Expression` instance is passed, it will be used as-is.
 959            distinct: set the DISTINCT flag if and only if this is true.
 960            dialect: the dialect used to parse the input expression.
 961            opts: other options to use to parse the input expressions.
 962
 963        Returns:
 964            The new Intersect expression.
 965        """
 966        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 967
 968    def except_(
 969        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 970    ) -> Unionable:
 971        """
 972        Builds an EXCEPT expression.
 973
 974        Example:
 975            >>> import sqlglot
 976            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 977            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 978
 979        Args:
 980            expression: the SQL code string.
 981                If an `Expression` instance is passed, it will be used as-is.
 982            distinct: set the DISTINCT flag if and only if this is true.
 983            dialect: the dialect used to parse the input expression.
 984            opts: other options to use to parse the input expressions.
 985
 986        Returns:
 987            The new Except expression.
 988        """
 989        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 990
 991
 992class UDTF(DerivedTable, Unionable):
 993    @property
 994    def selects(self) -> t.List[Expression]:
 995        alias = self.args.get("alias")
 996        return alias.columns if alias else []
 997
 998
 999class Cache(Expression):
1000    arg_types = {
1001        "this": True,
1002        "lazy": False,
1003        "options": False,
1004        "expression": False,
1005    }
1006
1007
1008class Uncache(Expression):
1009    arg_types = {"this": True, "exists": False}
1010
1011
1012class Refresh(Expression):
1013    pass
1014
1015
1016class DDL(Expression):
1017    @property
1018    def ctes(self):
1019        with_ = self.args.get("with")
1020        if not with_:
1021            return []
1022        return with_.expressions
1023
1024    @property
1025    def named_selects(self) -> t.List[str]:
1026        if isinstance(self.expression, Subqueryable):
1027            return self.expression.named_selects
1028        return []
1029
1030    @property
1031    def selects(self) -> t.List[Expression]:
1032        if isinstance(self.expression, Subqueryable):
1033            return self.expression.selects
1034        return []
1035
1036
1037class DML(Expression):
1038    def returning(
1039        self,
1040        expression: ExpOrStr,
1041        dialect: DialectType = None,
1042        copy: bool = True,
1043        **opts,
1044    ) -> DML:
1045        """
1046        Set the RETURNING expression. Not supported by all dialects.
1047
1048        Example:
1049            >>> delete("tbl").returning("*", dialect="postgres").sql()
1050            'DELETE FROM tbl RETURNING *'
1051
1052        Args:
1053            expression: the SQL code strings to parse.
1054                If an `Expression` instance is passed, it will be used as-is.
1055            dialect: the dialect used to parse the input expressions.
1056            copy: if `False`, modify this expression instance in-place.
1057            opts: other options to use to parse the input expressions.
1058
1059        Returns:
1060            Delete: the modified expression.
1061        """
1062        return _apply_builder(
1063            expression=expression,
1064            instance=self,
1065            arg="returning",
1066            prefix="RETURNING",
1067            dialect=dialect,
1068            copy=copy,
1069            into=Returning,
1070            **opts,
1071        )
1072
1073
1074class Create(DDL):
1075    arg_types = {
1076        "with": False,
1077        "this": True,
1078        "kind": True,
1079        "expression": False,
1080        "exists": False,
1081        "properties": False,
1082        "replace": False,
1083        "unique": False,
1084        "indexes": False,
1085        "no_schema_binding": False,
1086        "begin": False,
1087        "end": False,
1088        "clone": False,
1089    }
1090
1091
1092# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1093# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1094# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1095class Clone(Expression):
1096    arg_types = {"this": True, "shallow": False, "copy": False}
1097
1098
1099class Describe(Expression):
1100    arg_types = {"this": True, "kind": False, "expressions": False}
1101
1102
1103class Kill(Expression):
1104    arg_types = {"this": True, "kind": False}
1105
1106
1107class Pragma(Expression):
1108    pass
1109
1110
1111class Set(Expression):
1112    arg_types = {"expressions": False, "unset": False, "tag": False}
1113
1114
1115class SetItem(Expression):
1116    arg_types = {
1117        "this": False,
1118        "expressions": False,
1119        "kind": False,
1120        "collate": False,  # MySQL SET NAMES statement
1121        "global": False,
1122    }
1123
1124
1125class Show(Expression):
1126    arg_types = {
1127        "this": True,
1128        "target": False,
1129        "offset": False,
1130        "limit": False,
1131        "like": False,
1132        "where": False,
1133        "db": False,
1134        "scope": False,
1135        "scope_kind": False,
1136        "full": False,
1137        "mutex": False,
1138        "query": False,
1139        "channel": False,
1140        "global": False,
1141        "log": False,
1142        "position": False,
1143        "types": False,
1144    }
1145
1146
1147class UserDefinedFunction(Expression):
1148    arg_types = {"this": True, "expressions": False, "wrapped": False}
1149
1150
1151class CharacterSet(Expression):
1152    arg_types = {"this": True, "default": False}
1153
1154
1155class With(Expression):
1156    arg_types = {"expressions": True, "recursive": False}
1157
1158    @property
1159    def recursive(self) -> bool:
1160        return bool(self.args.get("recursive"))
1161
1162
1163class WithinGroup(Expression):
1164    arg_types = {"this": True, "expression": False}
1165
1166
1167# clickhouse supports scalar ctes
1168# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1169class CTE(DerivedTable):
1170    arg_types = {"this": True, "alias": True, "scalar": False}
1171
1172
1173class TableAlias(Expression):
1174    arg_types = {"this": False, "columns": False}
1175
1176    @property
1177    def columns(self):
1178        return self.args.get("columns") or []
1179
1180
1181class BitString(Condition):
1182    pass
1183
1184
1185class HexString(Condition):
1186    pass
1187
1188
1189class ByteString(Condition):
1190    pass
1191
1192
1193class RawString(Condition):
1194    pass
1195
1196
1197class UnicodeString(Condition):
1198    arg_types = {"this": True, "escape": False}
1199
1200
1201class Column(Condition):
1202    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1203
1204    @property
1205    def table(self) -> str:
1206        return self.text("table")
1207
1208    @property
1209    def db(self) -> str:
1210        return self.text("db")
1211
1212    @property
1213    def catalog(self) -> str:
1214        return self.text("catalog")
1215
1216    @property
1217    def output_name(self) -> str:
1218        return self.name
1219
1220    @property
1221    def parts(self) -> t.List[Identifier]:
1222        """Return the parts of a column in order catalog, db, table, name."""
1223        return [
1224            t.cast(Identifier, self.args[part])
1225            for part in ("catalog", "db", "table", "this")
1226            if self.args.get(part)
1227        ]
1228
1229    def to_dot(self) -> Dot | Identifier:
1230        """Converts the column into a dot expression."""
1231        parts = self.parts
1232        parent = self.parent
1233
1234        while parent:
1235            if isinstance(parent, Dot):
1236                parts.append(parent.expression)
1237            parent = parent.parent
1238
1239        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1240
1241
1242class ColumnPosition(Expression):
1243    arg_types = {"this": False, "position": True}
1244
1245
1246class ColumnDef(Expression):
1247    arg_types = {
1248        "this": True,
1249        "kind": False,
1250        "constraints": False,
1251        "exists": False,
1252        "position": False,
1253    }
1254
1255    @property
1256    def constraints(self) -> t.List[ColumnConstraint]:
1257        return self.args.get("constraints") or []
1258
1259
1260class AlterColumn(Expression):
1261    arg_types = {
1262        "this": True,
1263        "dtype": False,
1264        "collate": False,
1265        "using": False,
1266        "default": False,
1267        "drop": False,
1268    }
1269
1270
1271class RenameTable(Expression):
1272    pass
1273
1274
1275class SwapTable(Expression):
1276    pass
1277
1278
1279class Comment(Expression):
1280    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1281
1282
1283class Comprehension(Expression):
1284    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1285
1286
1287# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1288class MergeTreeTTLAction(Expression):
1289    arg_types = {
1290        "this": True,
1291        "delete": False,
1292        "recompress": False,
1293        "to_disk": False,
1294        "to_volume": False,
1295    }
1296
1297
1298# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1299class MergeTreeTTL(Expression):
1300    arg_types = {
1301        "expressions": True,
1302        "where": False,
1303        "group": False,
1304        "aggregates": False,
1305    }
1306
1307
1308# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1309class IndexConstraintOption(Expression):
1310    arg_types = {
1311        "key_block_size": False,
1312        "using": False,
1313        "parser": False,
1314        "comment": False,
1315        "visible": False,
1316        "engine_attr": False,
1317        "secondary_engine_attr": False,
1318    }
1319
1320
1321class ColumnConstraint(Expression):
1322    arg_types = {"this": False, "kind": True}
1323
1324    @property
1325    def kind(self) -> ColumnConstraintKind:
1326        return self.args["kind"]
1327
1328
1329class ColumnConstraintKind(Expression):
1330    pass
1331
1332
1333class AutoIncrementColumnConstraint(ColumnConstraintKind):
1334    pass
1335
1336
1337class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1338    arg_types = {"this": True, "expression": True}
1339
1340
1341class CaseSpecificColumnConstraint(ColumnConstraintKind):
1342    arg_types = {"not_": True}
1343
1344
1345class CharacterSetColumnConstraint(ColumnConstraintKind):
1346    arg_types = {"this": True}
1347
1348
1349class CheckColumnConstraint(ColumnConstraintKind):
1350    pass
1351
1352
1353class ClusteredColumnConstraint(ColumnConstraintKind):
1354    pass
1355
1356
1357class CollateColumnConstraint(ColumnConstraintKind):
1358    pass
1359
1360
1361class CommentColumnConstraint(ColumnConstraintKind):
1362    pass
1363
1364
1365class CompressColumnConstraint(ColumnConstraintKind):
1366    pass
1367
1368
1369class DateFormatColumnConstraint(ColumnConstraintKind):
1370    arg_types = {"this": True}
1371
1372
1373class DefaultColumnConstraint(ColumnConstraintKind):
1374    pass
1375
1376
1377class EncodeColumnConstraint(ColumnConstraintKind):
1378    pass
1379
1380
1381class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1382    # this: True -> ALWAYS, this: False -> BY DEFAULT
1383    arg_types = {
1384        "this": False,
1385        "expression": False,
1386        "on_null": False,
1387        "start": False,
1388        "increment": False,
1389        "minvalue": False,
1390        "maxvalue": False,
1391        "cycle": False,
1392    }
1393
1394
1395class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1396    arg_types = {"start": True, "hidden": False}
1397
1398
1399# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1400class IndexColumnConstraint(ColumnConstraintKind):
1401    arg_types = {
1402        "this": False,
1403        "schema": True,
1404        "kind": False,
1405        "index_type": False,
1406        "options": False,
1407    }
1408
1409
1410class InlineLengthColumnConstraint(ColumnConstraintKind):
1411    pass
1412
1413
1414class NonClusteredColumnConstraint(ColumnConstraintKind):
1415    pass
1416
1417
1418class NotForReplicationColumnConstraint(ColumnConstraintKind):
1419    arg_types = {}
1420
1421
1422class NotNullColumnConstraint(ColumnConstraintKind):
1423    arg_types = {"allow_null": False}
1424
1425
1426# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1427class OnUpdateColumnConstraint(ColumnConstraintKind):
1428    pass
1429
1430
1431# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1432class TransformColumnConstraint(ColumnConstraintKind):
1433    pass
1434
1435
1436class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1437    arg_types = {"desc": False}
1438
1439
1440class TitleColumnConstraint(ColumnConstraintKind):
1441    pass
1442
1443
1444class UniqueColumnConstraint(ColumnConstraintKind):
1445    arg_types = {"this": False, "index_type": False}
1446
1447
1448class UppercaseColumnConstraint(ColumnConstraintKind):
1449    arg_types: t.Dict[str, t.Any] = {}
1450
1451
1452class PathColumnConstraint(ColumnConstraintKind):
1453    pass
1454
1455
1456# computed column expression
1457# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1458class ComputedColumnConstraint(ColumnConstraintKind):
1459    arg_types = {"this": True, "persisted": False, "not_null": False}
1460
1461
1462class Constraint(Expression):
1463    arg_types = {"this": True, "expressions": True}
1464
1465
1466class Delete(DML):
1467    arg_types = {
1468        "with": False,
1469        "this": False,
1470        "using": False,
1471        "where": False,
1472        "returning": False,
1473        "limit": False,
1474        "tables": False,  # Multiple-Table Syntax (MySQL)
1475    }
1476
1477    def delete(
1478        self,
1479        table: ExpOrStr,
1480        dialect: DialectType = None,
1481        copy: bool = True,
1482        **opts,
1483    ) -> Delete:
1484        """
1485        Create a DELETE expression or replace the table on an existing DELETE expression.
1486
1487        Example:
1488            >>> delete("tbl").sql()
1489            'DELETE FROM tbl'
1490
1491        Args:
1492            table: the table from which to delete.
1493            dialect: the dialect used to parse the input expression.
1494            copy: if `False`, modify this expression instance in-place.
1495            opts: other options to use to parse the input expressions.
1496
1497        Returns:
1498            Delete: the modified expression.
1499        """
1500        return _apply_builder(
1501            expression=table,
1502            instance=self,
1503            arg="this",
1504            dialect=dialect,
1505            into=Table,
1506            copy=copy,
1507            **opts,
1508        )
1509
1510    def where(
1511        self,
1512        *expressions: t.Optional[ExpOrStr],
1513        append: bool = True,
1514        dialect: DialectType = None,
1515        copy: bool = True,
1516        **opts,
1517    ) -> Delete:
1518        """
1519        Append to or set the WHERE expressions.
1520
1521        Example:
1522            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1523            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1524
1525        Args:
1526            *expressions: the SQL code strings to parse.
1527                If an `Expression` instance is passed, it will be used as-is.
1528                Multiple expressions are combined with an AND operator.
1529            append: if `True`, AND the new expressions to any existing expression.
1530                Otherwise, this resets the expression.
1531            dialect: the dialect used to parse the input expressions.
1532            copy: if `False`, modify this expression instance in-place.
1533            opts: other options to use to parse the input expressions.
1534
1535        Returns:
1536            Delete: the modified expression.
1537        """
1538        return _apply_conjunction_builder(
1539            *expressions,
1540            instance=self,
1541            arg="where",
1542            append=append,
1543            into=Where,
1544            dialect=dialect,
1545            copy=copy,
1546            **opts,
1547        )
1548
1549
1550class Drop(Expression):
1551    arg_types = {
1552        "this": False,
1553        "kind": False,
1554        "exists": False,
1555        "temporary": False,
1556        "materialized": False,
1557        "cascade": False,
1558        "constraints": False,
1559        "purge": False,
1560    }
1561
1562
1563class Filter(Expression):
1564    arg_types = {"this": True, "expression": True}
1565
1566
1567class Check(Expression):
1568    pass
1569
1570
1571# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1572class Connect(Expression):
1573    arg_types = {"start": False, "connect": True}
1574
1575
1576class Prior(Expression):
1577    pass
1578
1579
1580class Directory(Expression):
1581    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1582    arg_types = {"this": True, "local": False, "row_format": False}
1583
1584
1585class ForeignKey(Expression):
1586    arg_types = {
1587        "expressions": True,
1588        "reference": False,
1589        "delete": False,
1590        "update": False,
1591    }
1592
1593
1594class ColumnPrefix(Expression):
1595    arg_types = {"this": True, "expression": True}
1596
1597
1598class PrimaryKey(Expression):
1599    arg_types = {"expressions": True, "options": False}
1600
1601
1602# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1603# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1604class Into(Expression):
1605    arg_types = {"this": True, "temporary": False, "unlogged": False}
1606
1607
1608class From(Expression):
1609    @property
1610    def name(self) -> str:
1611        return self.this.name
1612
1613    @property
1614    def alias_or_name(self) -> str:
1615        return self.this.alias_or_name
1616
1617
1618class Having(Expression):
1619    pass
1620
1621
1622class Hint(Expression):
1623    arg_types = {"expressions": True}
1624
1625
1626class JoinHint(Expression):
1627    arg_types = {"this": True, "expressions": True}
1628
1629
1630class Identifier(Expression):
1631    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1632
1633    @property
1634    def quoted(self) -> bool:
1635        return bool(self.args.get("quoted"))
1636
1637    @property
1638    def hashable_args(self) -> t.Any:
1639        return (self.this, self.quoted)
1640
1641    @property
1642    def output_name(self) -> str:
1643        return self.name
1644
1645
1646# https://www.postgresql.org/docs/current/indexes-opclass.html
1647class Opclass(Expression):
1648    arg_types = {"this": True, "expression": True}
1649
1650
1651class Index(Expression):
1652    arg_types = {
1653        "this": False,
1654        "table": False,
1655        "using": False,
1656        "where": False,
1657        "columns": False,
1658        "unique": False,
1659        "primary": False,
1660        "amp": False,  # teradata
1661        "partition_by": False,  # teradata
1662        "where": False,  # postgres partial indexes
1663    }
1664
1665
1666class Insert(DDL, DML):
1667    arg_types = {
1668        "with": False,
1669        "this": True,
1670        "expression": False,
1671        "conflict": False,
1672        "returning": False,
1673        "overwrite": False,
1674        "exists": False,
1675        "partition": False,
1676        "alternative": False,
1677        "where": False,
1678        "ignore": False,
1679        "by_name": False,
1680    }
1681
1682    def with_(
1683        self,
1684        alias: ExpOrStr,
1685        as_: ExpOrStr,
1686        recursive: t.Optional[bool] = None,
1687        append: bool = True,
1688        dialect: DialectType = None,
1689        copy: bool = True,
1690        **opts,
1691    ) -> Insert:
1692        """
1693        Append to or set the common table expressions.
1694
1695        Example:
1696            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1697            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1698
1699        Args:
1700            alias: the SQL code string to parse as the table name.
1701                If an `Expression` instance is passed, this is used as-is.
1702            as_: the SQL code string to parse as the table expression.
1703                If an `Expression` instance is passed, it will be used as-is.
1704            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1705            append: if `True`, add to any existing expressions.
1706                Otherwise, this resets the expressions.
1707            dialect: the dialect used to parse the input expression.
1708            copy: if `False`, modify this expression instance in-place.
1709            opts: other options to use to parse the input expressions.
1710
1711        Returns:
1712            The modified expression.
1713        """
1714        return _apply_cte_builder(
1715            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1716        )
1717
1718
1719class OnConflict(Expression):
1720    arg_types = {
1721        "duplicate": False,
1722        "expressions": False,
1723        "nothing": False,
1724        "key": False,
1725        "constraint": False,
1726    }
1727
1728
1729class Returning(Expression):
1730    arg_types = {"expressions": True, "into": False}
1731
1732
1733# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1734class Introducer(Expression):
1735    arg_types = {"this": True, "expression": True}
1736
1737
1738# national char, like n'utf8'
1739class National(Expression):
1740    pass
1741
1742
1743class LoadData(Expression):
1744    arg_types = {
1745        "this": True,
1746        "local": False,
1747        "overwrite": False,
1748        "inpath": True,
1749        "partition": False,
1750        "input_format": False,
1751        "serde": False,
1752    }
1753
1754
1755class Partition(Expression):
1756    arg_types = {"expressions": True}
1757
1758
1759class Fetch(Expression):
1760    arg_types = {
1761        "direction": False,
1762        "count": False,
1763        "percent": False,
1764        "with_ties": False,
1765    }
1766
1767
1768class Group(Expression):
1769    arg_types = {
1770        "expressions": False,
1771        "grouping_sets": False,
1772        "cube": False,
1773        "rollup": False,
1774        "totals": False,
1775        "all": False,
1776    }
1777
1778
1779class Lambda(Expression):
1780    arg_types = {"this": True, "expressions": True}
1781
1782
1783class Limit(Expression):
1784    arg_types = {"this": False, "expression": True, "offset": False}
1785
1786
1787class Literal(Condition):
1788    arg_types = {"this": True, "is_string": True}
1789
1790    @property
1791    def hashable_args(self) -> t.Any:
1792        return (self.this, self.args.get("is_string"))
1793
1794    @classmethod
1795    def number(cls, number) -> Literal:
1796        return cls(this=str(number), is_string=False)
1797
1798    @classmethod
1799    def string(cls, string) -> Literal:
1800        return cls(this=str(string), is_string=True)
1801
1802    @property
1803    def output_name(self) -> str:
1804        return self.name
1805
1806
1807class Join(Expression):
1808    arg_types = {
1809        "this": True,
1810        "on": False,
1811        "side": False,
1812        "kind": False,
1813        "using": False,
1814        "method": False,
1815        "global": False,
1816        "hint": False,
1817    }
1818
1819    @property
1820    def method(self) -> str:
1821        return self.text("method").upper()
1822
1823    @property
1824    def kind(self) -> str:
1825        return self.text("kind").upper()
1826
1827    @property
1828    def side(self) -> str:
1829        return self.text("side").upper()
1830
1831    @property
1832    def hint(self) -> str:
1833        return self.text("hint").upper()
1834
1835    @property
1836    def alias_or_name(self) -> str:
1837        return self.this.alias_or_name
1838
1839    def on(
1840        self,
1841        *expressions: t.Optional[ExpOrStr],
1842        append: bool = True,
1843        dialect: DialectType = None,
1844        copy: bool = True,
1845        **opts,
1846    ) -> Join:
1847        """
1848        Append to or set the ON expressions.
1849
1850        Example:
1851            >>> import sqlglot
1852            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1853            'JOIN x ON y = 1'
1854
1855        Args:
1856            *expressions: the SQL code strings to parse.
1857                If an `Expression` instance is passed, it will be used as-is.
1858                Multiple expressions are combined with an AND operator.
1859            append: if `True`, AND the new expressions to any existing expression.
1860                Otherwise, this resets the expression.
1861            dialect: the dialect used to parse the input expressions.
1862            copy: if `False`, modify this expression instance in-place.
1863            opts: other options to use to parse the input expressions.
1864
1865        Returns:
1866            The modified Join expression.
1867        """
1868        join = _apply_conjunction_builder(
1869            *expressions,
1870            instance=self,
1871            arg="on",
1872            append=append,
1873            dialect=dialect,
1874            copy=copy,
1875            **opts,
1876        )
1877
1878        if join.kind == "CROSS":
1879            join.set("kind", None)
1880
1881        return join
1882
1883    def using(
1884        self,
1885        *expressions: t.Optional[ExpOrStr],
1886        append: bool = True,
1887        dialect: DialectType = None,
1888        copy: bool = True,
1889        **opts,
1890    ) -> Join:
1891        """
1892        Append to or set the USING expressions.
1893
1894        Example:
1895            >>> import sqlglot
1896            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1897            'JOIN x USING (foo, bla)'
1898
1899        Args:
1900            *expressions: the SQL code strings to parse.
1901                If an `Expression` instance is passed, it will be used as-is.
1902            append: if `True`, concatenate the new expressions to the existing "using" list.
1903                Otherwise, this resets the expression.
1904            dialect: the dialect used to parse the input expressions.
1905            copy: if `False`, modify this expression instance in-place.
1906            opts: other options to use to parse the input expressions.
1907
1908        Returns:
1909            The modified Join expression.
1910        """
1911        join = _apply_list_builder(
1912            *expressions,
1913            instance=self,
1914            arg="using",
1915            append=append,
1916            dialect=dialect,
1917            copy=copy,
1918            **opts,
1919        )
1920
1921        if join.kind == "CROSS":
1922            join.set("kind", None)
1923
1924        return join
1925
1926
1927class Lateral(UDTF):
1928    arg_types = {
1929        "this": True,
1930        "view": False,
1931        "outer": False,
1932        "alias": False,
1933        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
1934    }
1935
1936
1937class MatchRecognize(Expression):
1938    arg_types = {
1939        "partition_by": False,
1940        "order": False,
1941        "measures": False,
1942        "rows": False,
1943        "after": False,
1944        "pattern": False,
1945        "define": False,
1946        "alias": False,
1947    }
1948
1949
1950# Clickhouse FROM FINAL modifier
1951# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1952class Final(Expression):
1953    pass
1954
1955
1956class Offset(Expression):
1957    arg_types = {"this": False, "expression": True}
1958
1959
1960class Order(Expression):
1961    arg_types = {"this": False, "expressions": True, "interpolate": False}
1962
1963
1964# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
1965class WithFill(Expression):
1966    arg_types = {"from": False, "to": False, "step": False}
1967
1968
1969# hive specific sorts
1970# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1971class Cluster(Order):
1972    pass
1973
1974
1975class Distribute(Order):
1976    pass
1977
1978
1979class Sort(Order):
1980    pass
1981
1982
1983class Ordered(Expression):
1984    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
1985
1986
1987class Property(Expression):
1988    arg_types = {"this": True, "value": True}
1989
1990
1991class AlgorithmProperty(Property):
1992    arg_types = {"this": True}
1993
1994
1995class AutoIncrementProperty(Property):
1996    arg_types = {"this": True}
1997
1998
1999# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2000class AutoRefreshProperty(Property):
2001    arg_types = {"this": True}
2002
2003
2004class BlockCompressionProperty(Property):
2005    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
2006
2007
2008class CharacterSetProperty(Property):
2009    arg_types = {"this": True, "default": True}
2010
2011
2012class ChecksumProperty(Property):
2013    arg_types = {"on": False, "default": False}
2014
2015
2016class CollateProperty(Property):
2017    arg_types = {"this": True, "default": False}
2018
2019
2020class CopyGrantsProperty(Property):
2021    arg_types = {}
2022
2023
2024class DataBlocksizeProperty(Property):
2025    arg_types = {
2026        "size": False,
2027        "units": False,
2028        "minimum": False,
2029        "maximum": False,
2030        "default": False,
2031    }
2032
2033
2034class DefinerProperty(Property):
2035    arg_types = {"this": True}
2036
2037
2038class DistKeyProperty(Property):
2039    arg_types = {"this": True}
2040
2041
2042class DistStyleProperty(Property):
2043    arg_types = {"this": True}
2044
2045
2046class EngineProperty(Property):
2047    arg_types = {"this": True}
2048
2049
2050class HeapProperty(Property):
2051    arg_types = {}
2052
2053
2054class ToTableProperty(Property):
2055    arg_types = {"this": True}
2056
2057
2058class ExecuteAsProperty(Property):
2059    arg_types = {"this": True}
2060
2061
2062class ExternalProperty(Property):
2063    arg_types = {"this": False}
2064
2065
2066class FallbackProperty(Property):
2067    arg_types = {"no": True, "protection": False}
2068
2069
2070class FileFormatProperty(Property):
2071    arg_types = {"this": True}
2072
2073
2074class FreespaceProperty(Property):
2075    arg_types = {"this": True, "percent": False}
2076
2077
2078class InputModelProperty(Property):
2079    arg_types = {"this": True}
2080
2081
2082class OutputModelProperty(Property):
2083    arg_types = {"this": True}
2084
2085
2086class IsolatedLoadingProperty(Property):
2087    arg_types = {
2088        "no": True,
2089        "concurrent": True,
2090        "for_all": True,
2091        "for_insert": True,
2092        "for_none": True,
2093    }
2094
2095
2096class JournalProperty(Property):
2097    arg_types = {
2098        "no": False,
2099        "dual": False,
2100        "before": False,
2101        "local": False,
2102        "after": False,
2103    }
2104
2105
2106class LanguageProperty(Property):
2107    arg_types = {"this": True}
2108
2109
2110# spark ddl
2111class ClusteredByProperty(Property):
2112    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2113
2114
2115class DictProperty(Property):
2116    arg_types = {"this": True, "kind": True, "settings": False}
2117
2118
2119class DictSubProperty(Property):
2120    pass
2121
2122
2123class DictRange(Property):
2124    arg_types = {"this": True, "min": True, "max": True}
2125
2126
2127# Clickhouse CREATE ... ON CLUSTER modifier
2128# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2129class OnCluster(Property):
2130    arg_types = {"this": True}
2131
2132
2133class LikeProperty(Property):
2134    arg_types = {"this": True, "expressions": False}
2135
2136
2137class LocationProperty(Property):
2138    arg_types = {"this": True}
2139
2140
2141class LockingProperty(Property):
2142    arg_types = {
2143        "this": False,
2144        "kind": True,
2145        "for_or_in": False,
2146        "lock_type": True,
2147        "override": False,
2148    }
2149
2150
2151class LogProperty(Property):
2152    arg_types = {"no": True}
2153
2154
2155class MaterializedProperty(Property):
2156    arg_types = {"this": False}
2157
2158
2159class MergeBlockRatioProperty(Property):
2160    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2161
2162
2163class NoPrimaryIndexProperty(Property):
2164    arg_types = {}
2165
2166
2167class OnProperty(Property):
2168    arg_types = {"this": True}
2169
2170
2171class OnCommitProperty(Property):
2172    arg_types = {"delete": False}
2173
2174
2175class PartitionedByProperty(Property):
2176    arg_types = {"this": True}
2177
2178
2179# https://www.postgresql.org/docs/current/sql-createtable.html
2180class PartitionBoundSpec(Expression):
2181    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2182    arg_types = {
2183        "this": False,
2184        "expression": False,
2185        "from_expressions": False,
2186        "to_expressions": False,
2187    }
2188
2189
2190class PartitionedOfProperty(Property):
2191    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2192    arg_types = {"this": True, "expression": True}
2193
2194
2195class RemoteWithConnectionModelProperty(Property):
2196    arg_types = {"this": True}
2197
2198
2199class ReturnsProperty(Property):
2200    arg_types = {"this": True, "is_table": False, "table": False}
2201
2202
2203class RowFormatProperty(Property):
2204    arg_types = {"this": True}
2205
2206
2207class RowFormatDelimitedProperty(Property):
2208    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2209    arg_types = {
2210        "fields": False,
2211        "escaped": False,
2212        "collection_items": False,
2213        "map_keys": False,
2214        "lines": False,
2215        "null": False,
2216        "serde": False,
2217    }
2218
2219
2220class RowFormatSerdeProperty(Property):
2221    arg_types = {"this": True, "serde_properties": False}
2222
2223
2224# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2225class QueryTransform(Expression):
2226    arg_types = {
2227        "expressions": True,
2228        "command_script": True,
2229        "schema": False,
2230        "row_format_before": False,
2231        "record_writer": False,
2232        "row_format_after": False,
2233        "record_reader": False,
2234    }
2235
2236
2237class SampleProperty(Property):
2238    arg_types = {"this": True}
2239
2240
2241class SchemaCommentProperty(Property):
2242    arg_types = {"this": True}
2243
2244
2245class SerdeProperties(Property):
2246    arg_types = {"expressions": True}
2247
2248
2249class SetProperty(Property):
2250    arg_types = {"multi": True}
2251
2252
2253class SettingsProperty(Property):
2254    arg_types = {"expressions": True}
2255
2256
2257class SortKeyProperty(Property):
2258    arg_types = {"this": True, "compound": False}
2259
2260
2261class SqlSecurityProperty(Property):
2262    arg_types = {"definer": True}
2263
2264
2265class StabilityProperty(Property):
2266    arg_types = {"this": True}
2267
2268
2269class TemporaryProperty(Property):
2270    arg_types = {}
2271
2272
2273class TransformModelProperty(Property):
2274    arg_types = {"expressions": True}
2275
2276
2277class TransientProperty(Property):
2278    arg_types = {"this": False}
2279
2280
2281class VolatileProperty(Property):
2282    arg_types = {"this": False}
2283
2284
2285class WithDataProperty(Property):
2286    arg_types = {"no": True, "statistics": False}
2287
2288
2289class WithJournalTableProperty(Property):
2290    arg_types = {"this": True}
2291
2292
2293class WithSystemVersioningProperty(Property):
2294    # this -> history table name, expression -> data consistency check
2295    arg_types = {"this": False, "expression": False}
2296
2297
2298class Properties(Expression):
2299    arg_types = {"expressions": True}
2300
2301    NAME_TO_PROPERTY = {
2302        "ALGORITHM": AlgorithmProperty,
2303        "AUTO_INCREMENT": AutoIncrementProperty,
2304        "CHARACTER SET": CharacterSetProperty,
2305        "CLUSTERED_BY": ClusteredByProperty,
2306        "COLLATE": CollateProperty,
2307        "COMMENT": SchemaCommentProperty,
2308        "DEFINER": DefinerProperty,
2309        "DISTKEY": DistKeyProperty,
2310        "DISTSTYLE": DistStyleProperty,
2311        "ENGINE": EngineProperty,
2312        "EXECUTE AS": ExecuteAsProperty,
2313        "FORMAT": FileFormatProperty,
2314        "LANGUAGE": LanguageProperty,
2315        "LOCATION": LocationProperty,
2316        "PARTITIONED_BY": PartitionedByProperty,
2317        "RETURNS": ReturnsProperty,
2318        "ROW_FORMAT": RowFormatProperty,
2319        "SORTKEY": SortKeyProperty,
2320    }
2321
2322    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2323
2324    # CREATE property locations
2325    # Form: schema specified
2326    #   create [POST_CREATE]
2327    #     table a [POST_NAME]
2328    #     (b int) [POST_SCHEMA]
2329    #     with ([POST_WITH])
2330    #     index (b) [POST_INDEX]
2331    #
2332    # Form: alias selection
2333    #   create [POST_CREATE]
2334    #     table a [POST_NAME]
2335    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2336    #     index (c) [POST_INDEX]
2337    class Location(AutoName):
2338        POST_CREATE = auto()
2339        POST_NAME = auto()
2340        POST_SCHEMA = auto()
2341        POST_WITH = auto()
2342        POST_ALIAS = auto()
2343        POST_EXPRESSION = auto()
2344        POST_INDEX = auto()
2345        UNSUPPORTED = auto()
2346
2347    @classmethod
2348    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2349        expressions = []
2350        for key, value in properties_dict.items():
2351            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2352            if property_cls:
2353                expressions.append(property_cls(this=convert(value)))
2354            else:
2355                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2356
2357        return cls(expressions=expressions)
2358
2359
2360class Qualify(Expression):
2361    pass
2362
2363
2364class InputOutputFormat(Expression):
2365    arg_types = {"input_format": False, "output_format": False}
2366
2367
2368# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2369class Return(Expression):
2370    pass
2371
2372
2373class Reference(Expression):
2374    arg_types = {"this": True, "expressions": False, "options": False}
2375
2376
2377class Tuple(Expression):
2378    arg_types = {"expressions": False}
2379
2380    def isin(
2381        self,
2382        *expressions: t.Any,
2383        query: t.Optional[ExpOrStr] = None,
2384        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2385        copy: bool = True,
2386        **opts,
2387    ) -> In:
2388        return In(
2389            this=maybe_copy(self, copy),
2390            expressions=[convert(e, copy=copy) for e in expressions],
2391            query=maybe_parse(query, copy=copy, **opts) if query else None,
2392            unnest=Unnest(
2393                expressions=[
2394                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2395                ]
2396            )
2397            if unnest
2398            else None,
2399        )
2400
2401
2402class Subqueryable(Unionable):
2403    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2404        """
2405        Convert this expression to an aliased expression that can be used as a Subquery.
2406
2407        Example:
2408            >>> subquery = Select().select("x").from_("tbl").subquery()
2409            >>> Select().select("x").from_(subquery).sql()
2410            'SELECT x FROM (SELECT x FROM tbl)'
2411
2412        Args:
2413            alias (str | Identifier): an optional alias for the subquery
2414            copy (bool): if `False`, modify this expression instance in-place.
2415
2416        Returns:
2417            Alias: the subquery
2418        """
2419        instance = maybe_copy(self, copy)
2420        if not isinstance(alias, Expression):
2421            alias = TableAlias(this=to_identifier(alias)) if alias else None
2422
2423        return Subquery(this=instance, alias=alias)
2424
2425    def limit(
2426        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2427    ) -> Select:
2428        raise NotImplementedError
2429
2430    @property
2431    def ctes(self):
2432        with_ = self.args.get("with")
2433        if not with_:
2434            return []
2435        return with_.expressions
2436
2437    @property
2438    def selects(self) -> t.List[Expression]:
2439        raise NotImplementedError("Subqueryable objects must implement `selects`")
2440
2441    @property
2442    def named_selects(self) -> t.List[str]:
2443        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2444
2445    def select(
2446        self,
2447        *expressions: t.Optional[ExpOrStr],
2448        append: bool = True,
2449        dialect: DialectType = None,
2450        copy: bool = True,
2451        **opts,
2452    ) -> Subqueryable:
2453        raise NotImplementedError("Subqueryable objects must implement `select`")
2454
2455    def with_(
2456        self,
2457        alias: ExpOrStr,
2458        as_: ExpOrStr,
2459        recursive: t.Optional[bool] = None,
2460        append: bool = True,
2461        dialect: DialectType = None,
2462        copy: bool = True,
2463        **opts,
2464    ) -> Subqueryable:
2465        """
2466        Append to or set the common table expressions.
2467
2468        Example:
2469            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2470            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2471
2472        Args:
2473            alias: the SQL code string to parse as the table name.
2474                If an `Expression` instance is passed, this is used as-is.
2475            as_: the SQL code string to parse as the table expression.
2476                If an `Expression` instance is passed, it will be used as-is.
2477            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2478            append: if `True`, add to any existing expressions.
2479                Otherwise, this resets the expressions.
2480            dialect: the dialect used to parse the input expression.
2481            copy: if `False`, modify this expression instance in-place.
2482            opts: other options to use to parse the input expressions.
2483
2484        Returns:
2485            The modified expression.
2486        """
2487        return _apply_cte_builder(
2488            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2489        )
2490
2491
2492QUERY_MODIFIERS = {
2493    "match": False,
2494    "laterals": False,
2495    "joins": False,
2496    "connect": False,
2497    "pivots": False,
2498    "where": False,
2499    "group": False,
2500    "having": False,
2501    "qualify": False,
2502    "windows": False,
2503    "distribute": False,
2504    "sort": False,
2505    "cluster": False,
2506    "order": False,
2507    "limit": False,
2508    "offset": False,
2509    "locks": False,
2510    "sample": False,
2511    "settings": False,
2512    "format": False,
2513}
2514
2515
2516# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2517class WithTableHint(Expression):
2518    arg_types = {"expressions": True}
2519
2520
2521# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2522class IndexTableHint(Expression):
2523    arg_types = {"this": True, "expressions": False, "target": False}
2524
2525
2526# https://docs.snowflake.com/en/sql-reference/constructs/at-before
2527class HistoricalData(Expression):
2528    arg_types = {"this": True, "kind": True, "expression": True}
2529
2530
2531class Table(Expression):
2532    arg_types = {
2533        "this": True,
2534        "alias": False,
2535        "db": False,
2536        "catalog": False,
2537        "laterals": False,
2538        "joins": False,
2539        "pivots": False,
2540        "hints": False,
2541        "system_time": False,
2542        "version": False,
2543        "format": False,
2544        "pattern": False,
2545        "ordinality": False,
2546        "when": False,
2547    }
2548
2549    @property
2550    def name(self) -> str:
2551        if isinstance(self.this, Func):
2552            return ""
2553        return self.this.name
2554
2555    @property
2556    def db(self) -> str:
2557        return self.text("db")
2558
2559    @property
2560    def catalog(self) -> str:
2561        return self.text("catalog")
2562
2563    @property
2564    def selects(self) -> t.List[Expression]:
2565        return []
2566
2567    @property
2568    def named_selects(self) -> t.List[str]:
2569        return []
2570
2571    @property
2572    def parts(self) -> t.List[Expression]:
2573        """Return the parts of a table in order catalog, db, table."""
2574        parts: t.List[Expression] = []
2575
2576        for arg in ("catalog", "db", "this"):
2577            part = self.args.get(arg)
2578
2579            if isinstance(part, Dot):
2580                parts.extend(part.flatten())
2581            elif isinstance(part, Expression):
2582                parts.append(part)
2583
2584        return parts
2585
2586    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
2587        parts = self.parts
2588        col = column(*reversed(parts[0:4]), *parts[4:], copy=copy)  # type: ignore
2589        alias = self.args.get("alias")
2590        if alias:
2591            col = alias_(col, alias.this, copy=copy)
2592        return col
2593
2594
2595class Union(Subqueryable):
2596    arg_types = {
2597        "with": False,
2598        "this": True,
2599        "expression": True,
2600        "distinct": False,
2601        "by_name": False,
2602        **QUERY_MODIFIERS,
2603    }
2604
2605    def limit(
2606        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2607    ) -> Select:
2608        """
2609        Set the LIMIT expression.
2610
2611        Example:
2612            >>> select("1").union(select("1")).limit(1).sql()
2613            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2614
2615        Args:
2616            expression: the SQL code string to parse.
2617                This can also be an integer.
2618                If a `Limit` instance is passed, this is used as-is.
2619                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2620            dialect: the dialect used to parse the input expression.
2621            copy: if `False`, modify this expression instance in-place.
2622            opts: other options to use to parse the input expressions.
2623
2624        Returns:
2625            The limited subqueryable.
2626        """
2627        return (
2628            select("*")
2629            .from_(self.subquery(alias="_l_0", copy=copy))
2630            .limit(expression, dialect=dialect, copy=False, **opts)
2631        )
2632
2633    def select(
2634        self,
2635        *expressions: t.Optional[ExpOrStr],
2636        append: bool = True,
2637        dialect: DialectType = None,
2638        copy: bool = True,
2639        **opts,
2640    ) -> Union:
2641        """Append to or set the SELECT of the union recursively.
2642
2643        Example:
2644            >>> from sqlglot import parse_one
2645            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2646            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2647
2648        Args:
2649            *expressions: the SQL code strings to parse.
2650                If an `Expression` instance is passed, it will be used as-is.
2651            append: if `True`, add to any existing expressions.
2652                Otherwise, this resets the expressions.
2653            dialect: the dialect used to parse the input expressions.
2654            copy: if `False`, modify this expression instance in-place.
2655            opts: other options to use to parse the input expressions.
2656
2657        Returns:
2658            Union: the modified expression.
2659        """
2660        this = self.copy() if copy else self
2661        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2662        this.expression.unnest().select(
2663            *expressions, append=append, dialect=dialect, copy=False, **opts
2664        )
2665        return this
2666
2667    @property
2668    def named_selects(self) -> t.List[str]:
2669        return self.this.unnest().named_selects
2670
2671    @property
2672    def is_star(self) -> bool:
2673        return self.this.is_star or self.expression.is_star
2674
2675    @property
2676    def selects(self) -> t.List[Expression]:
2677        return self.this.unnest().selects
2678
2679    @property
2680    def left(self) -> Expression:
2681        return self.this
2682
2683    @property
2684    def right(self) -> Expression:
2685        return self.expression
2686
2687
2688class Except(Union):
2689    pass
2690
2691
2692class Intersect(Union):
2693    pass
2694
2695
2696class Unnest(UDTF):
2697    arg_types = {
2698        "expressions": True,
2699        "alias": False,
2700        "offset": False,
2701    }
2702
2703
2704class Update(Expression):
2705    arg_types = {
2706        "with": False,
2707        "this": False,
2708        "expressions": True,
2709        "from": False,
2710        "where": False,
2711        "returning": False,
2712        "order": False,
2713        "limit": False,
2714    }
2715
2716
2717class Values(UDTF):
2718    arg_types = {"expressions": True, "alias": False}
2719
2720
2721class Var(Expression):
2722    pass
2723
2724
2725class Version(Expression):
2726    """
2727    Time travel, iceberg, bigquery etc
2728    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2729    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2730    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2731    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2732    this is either TIMESTAMP or VERSION
2733    kind is ("AS OF", "BETWEEN")
2734    """
2735
2736    arg_types = {"this": True, "kind": True, "expression": False}
2737
2738
2739class Schema(Expression):
2740    arg_types = {"this": False, "expressions": False}
2741
2742
2743# https://dev.mysql.com/doc/refman/8.0/en/select.html
2744# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2745class Lock(Expression):
2746    arg_types = {"update": True, "expressions": False, "wait": False}
2747
2748
2749class Select(Subqueryable):
2750    arg_types = {
2751        "with": False,
2752        "kind": False,
2753        "expressions": False,
2754        "hint": False,
2755        "distinct": False,
2756        "into": False,
2757        "from": False,
2758        **QUERY_MODIFIERS,
2759    }
2760
2761    def from_(
2762        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2763    ) -> Select:
2764        """
2765        Set the FROM expression.
2766
2767        Example:
2768            >>> Select().from_("tbl").select("x").sql()
2769            'SELECT x FROM tbl'
2770
2771        Args:
2772            expression : the SQL code strings to parse.
2773                If a `From` instance is passed, this is used as-is.
2774                If another `Expression` instance is passed, it will be wrapped in a `From`.
2775            dialect: the dialect used to parse the input expression.
2776            copy: if `False`, modify this expression instance in-place.
2777            opts: other options to use to parse the input expressions.
2778
2779        Returns:
2780            The modified Select expression.
2781        """
2782        return _apply_builder(
2783            expression=expression,
2784            instance=self,
2785            arg="from",
2786            into=From,
2787            prefix="FROM",
2788            dialect=dialect,
2789            copy=copy,
2790            **opts,
2791        )
2792
2793    def group_by(
2794        self,
2795        *expressions: t.Optional[ExpOrStr],
2796        append: bool = True,
2797        dialect: DialectType = None,
2798        copy: bool = True,
2799        **opts,
2800    ) -> Select:
2801        """
2802        Set the GROUP BY expression.
2803
2804        Example:
2805            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2806            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2807
2808        Args:
2809            *expressions: the SQL code strings to parse.
2810                If a `Group` instance is passed, this is used as-is.
2811                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2812                If nothing is passed in then a group by is not applied to the expression
2813            append: if `True`, add to any existing expressions.
2814                Otherwise, this flattens all the `Group` expression into a single expression.
2815            dialect: the dialect used to parse the input expression.
2816            copy: if `False`, modify this expression instance in-place.
2817            opts: other options to use to parse the input expressions.
2818
2819        Returns:
2820            The modified Select expression.
2821        """
2822        if not expressions:
2823            return self if not copy else self.copy()
2824
2825        return _apply_child_list_builder(
2826            *expressions,
2827            instance=self,
2828            arg="group",
2829            append=append,
2830            copy=copy,
2831            prefix="GROUP BY",
2832            into=Group,
2833            dialect=dialect,
2834            **opts,
2835        )
2836
2837    def order_by(
2838        self,
2839        *expressions: t.Optional[ExpOrStr],
2840        append: bool = True,
2841        dialect: DialectType = None,
2842        copy: bool = True,
2843        **opts,
2844    ) -> Select:
2845        """
2846        Set the ORDER BY expression.
2847
2848        Example:
2849            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2850            'SELECT x FROM tbl ORDER BY x DESC'
2851
2852        Args:
2853            *expressions: the SQL code strings to parse.
2854                If a `Group` instance is passed, this is used as-is.
2855                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2856            append: if `True`, add to any existing expressions.
2857                Otherwise, this flattens all the `Order` expression into a single expression.
2858            dialect: the dialect used to parse the input expression.
2859            copy: if `False`, modify this expression instance in-place.
2860            opts: other options to use to parse the input expressions.
2861
2862        Returns:
2863            The modified Select expression.
2864        """
2865        return _apply_child_list_builder(
2866            *expressions,
2867            instance=self,
2868            arg="order",
2869            append=append,
2870            copy=copy,
2871            prefix="ORDER BY",
2872            into=Order,
2873            dialect=dialect,
2874            **opts,
2875        )
2876
2877    def sort_by(
2878        self,
2879        *expressions: t.Optional[ExpOrStr],
2880        append: bool = True,
2881        dialect: DialectType = None,
2882        copy: bool = True,
2883        **opts,
2884    ) -> Select:
2885        """
2886        Set the SORT BY expression.
2887
2888        Example:
2889            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2890            'SELECT x FROM tbl SORT BY x DESC'
2891
2892        Args:
2893            *expressions: the SQL code strings to parse.
2894                If a `Group` instance is passed, this is used as-is.
2895                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2896            append: if `True`, add to any existing expressions.
2897                Otherwise, this flattens all the `Order` expression into a single expression.
2898            dialect: the dialect used to parse the input expression.
2899            copy: if `False`, modify this expression instance in-place.
2900            opts: other options to use to parse the input expressions.
2901
2902        Returns:
2903            The modified Select expression.
2904        """
2905        return _apply_child_list_builder(
2906            *expressions,
2907            instance=self,
2908            arg="sort",
2909            append=append,
2910            copy=copy,
2911            prefix="SORT BY",
2912            into=Sort,
2913            dialect=dialect,
2914            **opts,
2915        )
2916
2917    def cluster_by(
2918        self,
2919        *expressions: t.Optional[ExpOrStr],
2920        append: bool = True,
2921        dialect: DialectType = None,
2922        copy: bool = True,
2923        **opts,
2924    ) -> Select:
2925        """
2926        Set the CLUSTER BY expression.
2927
2928        Example:
2929            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2930            'SELECT x FROM tbl CLUSTER BY x DESC'
2931
2932        Args:
2933            *expressions: the SQL code strings to parse.
2934                If a `Group` instance is passed, this is used as-is.
2935                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2936            append: if `True`, add to any existing expressions.
2937                Otherwise, this flattens all the `Order` expression into a single expression.
2938            dialect: the dialect used to parse the input expression.
2939            copy: if `False`, modify this expression instance in-place.
2940            opts: other options to use to parse the input expressions.
2941
2942        Returns:
2943            The modified Select expression.
2944        """
2945        return _apply_child_list_builder(
2946            *expressions,
2947            instance=self,
2948            arg="cluster",
2949            append=append,
2950            copy=copy,
2951            prefix="CLUSTER BY",
2952            into=Cluster,
2953            dialect=dialect,
2954            **opts,
2955        )
2956
2957    def limit(
2958        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2959    ) -> Select:
2960        """
2961        Set the LIMIT expression.
2962
2963        Example:
2964            >>> Select().from_("tbl").select("x").limit(10).sql()
2965            'SELECT x FROM tbl LIMIT 10'
2966
2967        Args:
2968            expression: the SQL code string to parse.
2969                This can also be an integer.
2970                If a `Limit` instance is passed, this is used as-is.
2971                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2972            dialect: the dialect used to parse the input expression.
2973            copy: if `False`, modify this expression instance in-place.
2974            opts: other options to use to parse the input expressions.
2975
2976        Returns:
2977            Select: the modified expression.
2978        """
2979        return _apply_builder(
2980            expression=expression,
2981            instance=self,
2982            arg="limit",
2983            into=Limit,
2984            prefix="LIMIT",
2985            dialect=dialect,
2986            copy=copy,
2987            into_arg="expression",
2988            **opts,
2989        )
2990
2991    def offset(
2992        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2993    ) -> Select:
2994        """
2995        Set the OFFSET expression.
2996
2997        Example:
2998            >>> Select().from_("tbl").select("x").offset(10).sql()
2999            'SELECT x FROM tbl OFFSET 10'
3000
3001        Args:
3002            expression: the SQL code string to parse.
3003                This can also be an integer.
3004                If a `Offset` instance is passed, this is used as-is.
3005                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
3006            dialect: the dialect used to parse the input expression.
3007            copy: if `False`, modify this expression instance in-place.
3008            opts: other options to use to parse the input expressions.
3009
3010        Returns:
3011            The modified Select expression.
3012        """
3013        return _apply_builder(
3014            expression=expression,
3015            instance=self,
3016            arg="offset",
3017            into=Offset,
3018            prefix="OFFSET",
3019            dialect=dialect,
3020            copy=copy,
3021            into_arg="expression",
3022            **opts,
3023        )
3024
3025    def select(
3026        self,
3027        *expressions: t.Optional[ExpOrStr],
3028        append: bool = True,
3029        dialect: DialectType = None,
3030        copy: bool = True,
3031        **opts,
3032    ) -> Select:
3033        """
3034        Append to or set the SELECT expressions.
3035
3036        Example:
3037            >>> Select().select("x", "y").sql()
3038            'SELECT x, y'
3039
3040        Args:
3041            *expressions: the SQL code strings to parse.
3042                If an `Expression` instance is passed, it will be used as-is.
3043            append: if `True`, add to any existing expressions.
3044                Otherwise, this resets the expressions.
3045            dialect: the dialect used to parse the input expressions.
3046            copy: if `False`, modify this expression instance in-place.
3047            opts: other options to use to parse the input expressions.
3048
3049        Returns:
3050            The modified Select expression.
3051        """
3052        return _apply_list_builder(
3053            *expressions,
3054            instance=self,
3055            arg="expressions",
3056            append=append,
3057            dialect=dialect,
3058            copy=copy,
3059            **opts,
3060        )
3061
3062    def lateral(
3063        self,
3064        *expressions: t.Optional[ExpOrStr],
3065        append: bool = True,
3066        dialect: DialectType = None,
3067        copy: bool = True,
3068        **opts,
3069    ) -> Select:
3070        """
3071        Append to or set the LATERAL expressions.
3072
3073        Example:
3074            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3075            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3076
3077        Args:
3078            *expressions: the SQL code strings to parse.
3079                If an `Expression` instance is passed, it will be used as-is.
3080            append: if `True`, add to any existing expressions.
3081                Otherwise, this resets the expressions.
3082            dialect: the dialect used to parse the input expressions.
3083            copy: if `False`, modify this expression instance in-place.
3084            opts: other options to use to parse the input expressions.
3085
3086        Returns:
3087            The modified Select expression.
3088        """
3089        return _apply_list_builder(
3090            *expressions,
3091            instance=self,
3092            arg="laterals",
3093            append=append,
3094            into=Lateral,
3095            prefix="LATERAL VIEW",
3096            dialect=dialect,
3097            copy=copy,
3098            **opts,
3099        )
3100
3101    def join(
3102        self,
3103        expression: ExpOrStr,
3104        on: t.Optional[ExpOrStr] = None,
3105        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3106        append: bool = True,
3107        join_type: t.Optional[str] = None,
3108        join_alias: t.Optional[Identifier | str] = None,
3109        dialect: DialectType = None,
3110        copy: bool = True,
3111        **opts,
3112    ) -> Select:
3113        """
3114        Append to or set the JOIN expressions.
3115
3116        Example:
3117            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3118            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3119
3120            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3121            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3122
3123            Use `join_type` to change the type of join:
3124
3125            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3126            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3127
3128        Args:
3129            expression: the SQL code string to parse.
3130                If an `Expression` instance is passed, it will be used as-is.
3131            on: optionally specify the join "on" criteria as a SQL string.
3132                If an `Expression` instance is passed, it will be used as-is.
3133            using: optionally specify the join "using" criteria as a SQL string.
3134                If an `Expression` instance is passed, it will be used as-is.
3135            append: if `True`, add to any existing expressions.
3136                Otherwise, this resets the expressions.
3137            join_type: if set, alter the parsed join type.
3138            join_alias: an optional alias for the joined source.
3139            dialect: the dialect used to parse the input expressions.
3140            copy: if `False`, modify this expression instance in-place.
3141            opts: other options to use to parse the input expressions.
3142
3143        Returns:
3144            Select: the modified expression.
3145        """
3146        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3147
3148        try:
3149            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3150        except ParseError:
3151            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3152
3153        join = expression if isinstance(expression, Join) else Join(this=expression)
3154
3155        if isinstance(join.this, Select):
3156            join.this.replace(join.this.subquery())
3157
3158        if join_type:
3159            method: t.Optional[Token]
3160            side: t.Optional[Token]
3161            kind: t.Optional[Token]
3162
3163            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3164
3165            if method:
3166                join.set("method", method.text)
3167            if side:
3168                join.set("side", side.text)
3169            if kind:
3170                join.set("kind", kind.text)
3171
3172        if on:
3173            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3174            join.set("on", on)
3175
3176        if using:
3177            join = _apply_list_builder(
3178                *ensure_list(using),
3179                instance=join,
3180                arg="using",
3181                append=append,
3182                copy=copy,
3183                into=Identifier,
3184                **opts,
3185            )
3186
3187        if join_alias:
3188            join.set("this", alias_(join.this, join_alias, table=True))
3189
3190        return _apply_list_builder(
3191            join,
3192            instance=self,
3193            arg="joins",
3194            append=append,
3195            copy=copy,
3196            **opts,
3197        )
3198
3199    def where(
3200        self,
3201        *expressions: t.Optional[ExpOrStr],
3202        append: bool = True,
3203        dialect: DialectType = None,
3204        copy: bool = True,
3205        **opts,
3206    ) -> Select:
3207        """
3208        Append to or set the WHERE expressions.
3209
3210        Example:
3211            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3212            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3213
3214        Args:
3215            *expressions: the SQL code strings to parse.
3216                If an `Expression` instance is passed, it will be used as-is.
3217                Multiple expressions are combined with an AND operator.
3218            append: if `True`, AND the new expressions to any existing expression.
3219                Otherwise, this resets the expression.
3220            dialect: the dialect used to parse the input expressions.
3221            copy: if `False`, modify this expression instance in-place.
3222            opts: other options to use to parse the input expressions.
3223
3224        Returns:
3225            Select: the modified expression.
3226        """
3227        return _apply_conjunction_builder(
3228            *expressions,
3229            instance=self,
3230            arg="where",
3231            append=append,
3232            into=Where,
3233            dialect=dialect,
3234            copy=copy,
3235            **opts,
3236        )
3237
3238    def having(
3239        self,
3240        *expressions: t.Optional[ExpOrStr],
3241        append: bool = True,
3242        dialect: DialectType = None,
3243        copy: bool = True,
3244        **opts,
3245    ) -> Select:
3246        """
3247        Append to or set the HAVING expressions.
3248
3249        Example:
3250            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3251            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3252
3253        Args:
3254            *expressions: the SQL code strings to parse.
3255                If an `Expression` instance is passed, it will be used as-is.
3256                Multiple expressions are combined with an AND operator.
3257            append: if `True`, AND the new expressions to any existing expression.
3258                Otherwise, this resets the expression.
3259            dialect: the dialect used to parse the input expressions.
3260            copy: if `False`, modify this expression instance in-place.
3261            opts: other options to use to parse the input expressions.
3262
3263        Returns:
3264            The modified Select expression.
3265        """
3266        return _apply_conjunction_builder(
3267            *expressions,
3268            instance=self,
3269            arg="having",
3270            append=append,
3271            into=Having,
3272            dialect=dialect,
3273            copy=copy,
3274            **opts,
3275        )
3276
3277    def window(
3278        self,
3279        *expressions: t.Optional[ExpOrStr],
3280        append: bool = True,
3281        dialect: DialectType = None,
3282        copy: bool = True,
3283        **opts,
3284    ) -> Select:
3285        return _apply_list_builder(
3286            *expressions,
3287            instance=self,
3288            arg="windows",
3289            append=append,
3290            into=Window,
3291            dialect=dialect,
3292            copy=copy,
3293            **opts,
3294        )
3295
3296    def qualify(
3297        self,
3298        *expressions: t.Optional[ExpOrStr],
3299        append: bool = True,
3300        dialect: DialectType = None,
3301        copy: bool = True,
3302        **opts,
3303    ) -> Select:
3304        return _apply_conjunction_builder(
3305            *expressions,
3306            instance=self,
3307            arg="qualify",
3308            append=append,
3309            into=Qualify,
3310            dialect=dialect,
3311            copy=copy,
3312            **opts,
3313        )
3314
3315    def distinct(
3316        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3317    ) -> Select:
3318        """
3319        Set the OFFSET expression.
3320
3321        Example:
3322            >>> Select().from_("tbl").select("x").distinct().sql()
3323            'SELECT DISTINCT x FROM tbl'
3324
3325        Args:
3326            ons: the expressions to distinct on
3327            distinct: whether the Select should be distinct
3328            copy: if `False`, modify this expression instance in-place.
3329
3330        Returns:
3331            Select: the modified expression.
3332        """
3333        instance = maybe_copy(self, copy)
3334        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3335        instance.set("distinct", Distinct(on=on) if distinct else None)
3336        return instance
3337
3338    def ctas(
3339        self,
3340        table: ExpOrStr,
3341        properties: t.Optional[t.Dict] = None,
3342        dialect: DialectType = None,
3343        copy: bool = True,
3344        **opts,
3345    ) -> Create:
3346        """
3347        Convert this expression to a CREATE TABLE AS statement.
3348
3349        Example:
3350            >>> Select().select("*").from_("tbl").ctas("x").sql()
3351            'CREATE TABLE x AS SELECT * FROM tbl'
3352
3353        Args:
3354            table: the SQL code string to parse as the table name.
3355                If another `Expression` instance is passed, it will be used as-is.
3356            properties: an optional mapping of table properties
3357            dialect: the dialect used to parse the input table.
3358            copy: if `False`, modify this expression instance in-place.
3359            opts: other options to use to parse the input table.
3360
3361        Returns:
3362            The new Create expression.
3363        """
3364        instance = maybe_copy(self, copy)
3365        table_expression = maybe_parse(
3366            table,
3367            into=Table,
3368            dialect=dialect,
3369            **opts,
3370        )
3371        properties_expression = None
3372        if properties:
3373            properties_expression = Properties.from_dict(properties)
3374
3375        return Create(
3376            this=table_expression,
3377            kind="TABLE",
3378            expression=instance,
3379            properties=properties_expression,
3380        )
3381
3382    def lock(self, update: bool = True, copy: bool = True) -> Select:
3383        """
3384        Set the locking read mode for this expression.
3385
3386        Examples:
3387            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3388            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3389
3390            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3391            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3392
3393        Args:
3394            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3395            copy: if `False`, modify this expression instance in-place.
3396
3397        Returns:
3398            The modified expression.
3399        """
3400        inst = maybe_copy(self, copy)
3401        inst.set("locks", [Lock(update=update)])
3402
3403        return inst
3404
3405    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3406        """
3407        Set hints for this expression.
3408
3409        Examples:
3410            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3411            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3412
3413        Args:
3414            hints: The SQL code strings to parse as the hints.
3415                If an `Expression` instance is passed, it will be used as-is.
3416            dialect: The dialect used to parse the hints.
3417            copy: If `False`, modify this expression instance in-place.
3418
3419        Returns:
3420            The modified expression.
3421        """
3422        inst = maybe_copy(self, copy)
3423        inst.set(
3424            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3425        )
3426
3427        return inst
3428
3429    @property
3430    def named_selects(self) -> t.List[str]:
3431        return [e.output_name for e in self.expressions if e.alias_or_name]
3432
3433    @property
3434    def is_star(self) -> bool:
3435        return any(expression.is_star for expression in self.expressions)
3436
3437    @property
3438    def selects(self) -> t.List[Expression]:
3439        return self.expressions
3440
3441
3442class Subquery(DerivedTable, Unionable):
3443    arg_types = {
3444        "this": True,
3445        "alias": False,
3446        "with": False,
3447        **QUERY_MODIFIERS,
3448    }
3449
3450    def unnest(self):
3451        """
3452        Returns the first non subquery.
3453        """
3454        expression = self
3455        while isinstance(expression, Subquery):
3456            expression = expression.this
3457        return expression
3458
3459    def unwrap(self) -> Subquery:
3460        expression = self
3461        while expression.same_parent and expression.is_wrapper:
3462            expression = t.cast(Subquery, expression.parent)
3463        return expression
3464
3465    @property
3466    def is_wrapper(self) -> bool:
3467        """
3468        Whether this Subquery acts as a simple wrapper around another expression.
3469
3470        SELECT * FROM (((SELECT * FROM t)))
3471                      ^
3472                      This corresponds to a "wrapper" Subquery node
3473        """
3474        return all(v is None for k, v in self.args.items() if k != "this")
3475
3476    @property
3477    def is_star(self) -> bool:
3478        return self.this.is_star
3479
3480    @property
3481    def output_name(self) -> str:
3482        return self.alias
3483
3484
3485class TableSample(Expression):
3486    arg_types = {
3487        "this": False,
3488        "expressions": False,
3489        "method": False,
3490        "bucket_numerator": False,
3491        "bucket_denominator": False,
3492        "bucket_field": False,
3493        "percent": False,
3494        "rows": False,
3495        "size": False,
3496        "seed": False,
3497    }
3498
3499
3500class Tag(Expression):
3501    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3502
3503    arg_types = {
3504        "this": False,
3505        "prefix": False,
3506        "postfix": False,
3507    }
3508
3509
3510# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3511# https://duckdb.org/docs/sql/statements/pivot
3512class Pivot(Expression):
3513    arg_types = {
3514        "this": False,
3515        "alias": False,
3516        "expressions": False,
3517        "field": False,
3518        "unpivot": False,
3519        "using": False,
3520        "group": False,
3521        "columns": False,
3522        "include_nulls": False,
3523    }
3524
3525
3526class Window(Condition):
3527    arg_types = {
3528        "this": True,
3529        "partition_by": False,
3530        "order": False,
3531        "spec": False,
3532        "alias": False,
3533        "over": False,
3534        "first": False,
3535    }
3536
3537
3538class WindowSpec(Expression):
3539    arg_types = {
3540        "kind": False,
3541        "start": False,
3542        "start_side": False,
3543        "end": False,
3544        "end_side": False,
3545    }
3546
3547
3548class Where(Expression):
3549    pass
3550
3551
3552class Star(Expression):
3553    arg_types = {"except": False, "replace": False}
3554
3555    @property
3556    def name(self) -> str:
3557        return "*"
3558
3559    @property
3560    def output_name(self) -> str:
3561        return self.name
3562
3563
3564class Parameter(Condition):
3565    arg_types = {"this": True, "expression": False}
3566
3567
3568class SessionParameter(Condition):
3569    arg_types = {"this": True, "kind": False}
3570
3571
3572class Placeholder(Condition):
3573    arg_types = {"this": False, "kind": False}
3574
3575
3576class Null(Condition):
3577    arg_types: t.Dict[str, t.Any] = {}
3578
3579    @property
3580    def name(self) -> str:
3581        return "NULL"
3582
3583
3584class Boolean(Condition):
3585    pass
3586
3587
3588class DataTypeParam(Expression):
3589    arg_types = {"this": True, "expression": False}
3590
3591
3592class DataType(Expression):
3593    arg_types = {
3594        "this": True,
3595        "expressions": False,
3596        "nested": False,
3597        "values": False,
3598        "prefix": False,
3599        "kind": False,
3600    }
3601
3602    class Type(AutoName):
3603        ARRAY = auto()
3604        BIGDECIMAL = auto()
3605        BIGINT = auto()
3606        BIGSERIAL = auto()
3607        BINARY = auto()
3608        BIT = auto()
3609        BOOLEAN = auto()
3610        CHAR = auto()
3611        DATE = auto()
3612        DATEMULTIRANGE = auto()
3613        DATERANGE = auto()
3614        DATETIME = auto()
3615        DATETIME64 = auto()
3616        DECIMAL = auto()
3617        DOUBLE = auto()
3618        ENUM = auto()
3619        ENUM8 = auto()
3620        ENUM16 = auto()
3621        FIXEDSTRING = auto()
3622        FLOAT = auto()
3623        GEOGRAPHY = auto()
3624        GEOMETRY = auto()
3625        HLLSKETCH = auto()
3626        HSTORE = auto()
3627        IMAGE = auto()
3628        INET = auto()
3629        INT = auto()
3630        INT128 = auto()
3631        INT256 = auto()
3632        INT4MULTIRANGE = auto()
3633        INT4RANGE = auto()
3634        INT8MULTIRANGE = auto()
3635        INT8RANGE = auto()
3636        INTERVAL = auto()
3637        IPADDRESS = auto()
3638        IPPREFIX = auto()
3639        JSON = auto()
3640        JSONB = auto()
3641        LONGBLOB = auto()
3642        LONGTEXT = auto()
3643        LOWCARDINALITY = auto()
3644        MAP = auto()
3645        MEDIUMBLOB = auto()
3646        MEDIUMINT = auto()
3647        MEDIUMTEXT = auto()
3648        MONEY = auto()
3649        NCHAR = auto()
3650        NESTED = auto()
3651        NULL = auto()
3652        NULLABLE = auto()
3653        NUMMULTIRANGE = auto()
3654        NUMRANGE = auto()
3655        NVARCHAR = auto()
3656        OBJECT = auto()
3657        ROWVERSION = auto()
3658        SERIAL = auto()
3659        SET = auto()
3660        SMALLINT = auto()
3661        SMALLMONEY = auto()
3662        SMALLSERIAL = auto()
3663        STRUCT = auto()
3664        SUPER = auto()
3665        TEXT = auto()
3666        TINYBLOB = auto()
3667        TINYTEXT = auto()
3668        TIME = auto()
3669        TIMETZ = auto()
3670        TIMESTAMP = auto()
3671        TIMESTAMPLTZ = auto()
3672        TIMESTAMPTZ = auto()
3673        TIMESTAMP_S = auto()
3674        TIMESTAMP_MS = auto()
3675        TIMESTAMP_NS = auto()
3676        TINYINT = auto()
3677        TSMULTIRANGE = auto()
3678        TSRANGE = auto()
3679        TSTZMULTIRANGE = auto()
3680        TSTZRANGE = auto()
3681        UBIGINT = auto()
3682        UINT = auto()
3683        UINT128 = auto()
3684        UINT256 = auto()
3685        UMEDIUMINT = auto()
3686        UDECIMAL = auto()
3687        UNIQUEIDENTIFIER = auto()
3688        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3689        USERDEFINED = "USER-DEFINED"
3690        USMALLINT = auto()
3691        UTINYINT = auto()
3692        UUID = auto()
3693        VARBINARY = auto()
3694        VARCHAR = auto()
3695        VARIANT = auto()
3696        XML = auto()
3697        YEAR = auto()
3698
3699    TEXT_TYPES = {
3700        Type.CHAR,
3701        Type.NCHAR,
3702        Type.VARCHAR,
3703        Type.NVARCHAR,
3704        Type.TEXT,
3705    }
3706
3707    INTEGER_TYPES = {
3708        Type.INT,
3709        Type.TINYINT,
3710        Type.SMALLINT,
3711        Type.BIGINT,
3712        Type.INT128,
3713        Type.INT256,
3714        Type.BIT,
3715    }
3716
3717    FLOAT_TYPES = {
3718        Type.FLOAT,
3719        Type.DOUBLE,
3720    }
3721
3722    NUMERIC_TYPES = {
3723        *INTEGER_TYPES,
3724        *FLOAT_TYPES,
3725    }
3726
3727    TEMPORAL_TYPES = {
3728        Type.TIME,
3729        Type.TIMETZ,
3730        Type.TIMESTAMP,
3731        Type.TIMESTAMPTZ,
3732        Type.TIMESTAMPLTZ,
3733        Type.TIMESTAMP_S,
3734        Type.TIMESTAMP_MS,
3735        Type.TIMESTAMP_NS,
3736        Type.DATE,
3737        Type.DATETIME,
3738        Type.DATETIME64,
3739    }
3740
3741    @classmethod
3742    def build(
3743        cls,
3744        dtype: DATA_TYPE,
3745        dialect: DialectType = None,
3746        udt: bool = False,
3747        **kwargs,
3748    ) -> DataType:
3749        """
3750        Constructs a DataType object.
3751
3752        Args:
3753            dtype: the data type of interest.
3754            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3755            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3756                DataType, thus creating a user-defined type.
3757            kawrgs: additional arguments to pass in the constructor of DataType.
3758
3759        Returns:
3760            The constructed DataType object.
3761        """
3762        from sqlglot import parse_one
3763
3764        if isinstance(dtype, str):
3765            if dtype.upper() == "UNKNOWN":
3766                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3767
3768            try:
3769                data_type_exp = parse_one(
3770                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3771                )
3772            except ParseError:
3773                if udt:
3774                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3775                raise
3776        elif isinstance(dtype, DataType.Type):
3777            data_type_exp = DataType(this=dtype)
3778        elif isinstance(dtype, DataType):
3779            return dtype
3780        else:
3781            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3782
3783        return DataType(**{**data_type_exp.args, **kwargs})
3784
3785    def is_type(self, *dtypes: DATA_TYPE) -> bool:
3786        """
3787        Checks whether this DataType matches one of the provided data types. Nested types or precision
3788        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3789
3790        Args:
3791            dtypes: the data types to compare this DataType to.
3792
3793        Returns:
3794            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3795        """
3796        for dtype in dtypes:
3797            other = DataType.build(dtype, udt=True)
3798
3799            if (
3800                other.expressions
3801                or self.this == DataType.Type.USERDEFINED
3802                or other.this == DataType.Type.USERDEFINED
3803            ):
3804                matches = self == other
3805            else:
3806                matches = self.this == other.this
3807
3808            if matches:
3809                return True
3810        return False
3811
3812
3813DATA_TYPE = t.Union[str, DataType, DataType.Type]
3814
3815
3816# https://www.postgresql.org/docs/15/datatype-pseudo.html
3817class PseudoType(DataType):
3818    arg_types = {"this": True}
3819
3820
3821# https://www.postgresql.org/docs/15/datatype-oid.html
3822class ObjectIdentifier(DataType):
3823    arg_types = {"this": True}
3824
3825
3826# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3827class SubqueryPredicate(Predicate):
3828    pass
3829
3830
3831class All(SubqueryPredicate):
3832    pass
3833
3834
3835class Any(SubqueryPredicate):
3836    pass
3837
3838
3839class Exists(SubqueryPredicate):
3840    pass
3841
3842
3843# Commands to interact with the databases or engines. For most of the command
3844# expressions we parse whatever comes after the command's name as a string.
3845class Command(Expression):
3846    arg_types = {"this": True, "expression": False}
3847
3848
3849class Transaction(Expression):
3850    arg_types = {"this": False, "modes": False, "mark": False}
3851
3852
3853class Commit(Expression):
3854    arg_types = {"chain": False, "this": False, "durability": False}
3855
3856
3857class Rollback(Expression):
3858    arg_types = {"savepoint": False, "this": False}
3859
3860
3861class AlterTable(Expression):
3862    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3863
3864
3865class AddConstraint(Expression):
3866    arg_types = {"this": False, "expression": False, "enforced": False}
3867
3868
3869class DropPartition(Expression):
3870    arg_types = {"expressions": True, "exists": False}
3871
3872
3873# Binary expressions like (ADD a b)
3874class Binary(Condition):
3875    arg_types = {"this": True, "expression": True}
3876
3877    @property
3878    def left(self) -> Expression:
3879        return self.this
3880
3881    @property
3882    def right(self) -> Expression:
3883        return self.expression
3884
3885
3886class Add(Binary):
3887    pass
3888
3889
3890class Connector(Binary):
3891    pass
3892
3893
3894class And(Connector):
3895    pass
3896
3897
3898class Or(Connector):
3899    pass
3900
3901
3902class BitwiseAnd(Binary):
3903    pass
3904
3905
3906class BitwiseLeftShift(Binary):
3907    pass
3908
3909
3910class BitwiseOr(Binary):
3911    pass
3912
3913
3914class BitwiseRightShift(Binary):
3915    pass
3916
3917
3918class BitwiseXor(Binary):
3919    pass
3920
3921
3922class Div(Binary):
3923    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3924
3925
3926class Overlaps(Binary):
3927    pass
3928
3929
3930class Dot(Binary):
3931    @property
3932    def name(self) -> str:
3933        return self.expression.name
3934
3935    @property
3936    def output_name(self) -> str:
3937        return self.name
3938
3939    @classmethod
3940    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3941        """Build a Dot object with a sequence of expressions."""
3942        if len(expressions) < 2:
3943            raise ValueError(f"Dot requires >= 2 expressions.")
3944
3945        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3946
3947    @property
3948    def parts(self) -> t.List[Expression]:
3949        """Return the parts of a table / column in order catalog, db, table."""
3950        this, *parts = self.flatten()
3951
3952        parts.reverse()
3953
3954        for arg in ("this", "table", "db", "catalog"):
3955            part = this.args.get(arg)
3956
3957            if isinstance(part, Expression):
3958                parts.append(part)
3959
3960        parts.reverse()
3961        return parts
3962
3963
3964class DPipe(Binary):
3965    arg_types = {"this": True, "expression": True, "safe": False}
3966
3967
3968class EQ(Binary, Predicate):
3969    pass
3970
3971
3972class NullSafeEQ(Binary, Predicate):
3973    pass
3974
3975
3976class NullSafeNEQ(Binary, Predicate):
3977    pass
3978
3979
3980# Represents e.g. := in DuckDB which is mostly used for setting parameters
3981class PropertyEQ(Binary):
3982    pass
3983
3984
3985class Distance(Binary):
3986    pass
3987
3988
3989class Escape(Binary):
3990    pass
3991
3992
3993class Glob(Binary, Predicate):
3994    pass
3995
3996
3997class GT(Binary, Predicate):
3998    pass
3999
4000
4001class GTE(Binary, Predicate):
4002    pass
4003
4004
4005class ILike(Binary, Predicate):
4006    pass
4007
4008
4009class ILikeAny(Binary, Predicate):
4010    pass
4011
4012
4013class IntDiv(Binary):
4014    pass
4015
4016
4017class Is(Binary, Predicate):
4018    pass
4019
4020
4021class Kwarg(Binary):
4022    """Kwarg in special functions like func(kwarg => y)."""
4023
4024
4025class Like(Binary, Predicate):
4026    pass
4027
4028
4029class LikeAny(Binary, Predicate):
4030    pass
4031
4032
4033class LT(Binary, Predicate):
4034    pass
4035
4036
4037class LTE(Binary, Predicate):
4038    pass
4039
4040
4041class Mod(Binary):
4042    pass
4043
4044
4045class Mul(Binary):
4046    pass
4047
4048
4049class NEQ(Binary, Predicate):
4050    pass
4051
4052
4053# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4054class Operator(Binary):
4055    arg_types = {"this": True, "operator": True, "expression": True}
4056
4057
4058class SimilarTo(Binary, Predicate):
4059    pass
4060
4061
4062class Slice(Binary):
4063    arg_types = {"this": False, "expression": False}
4064
4065
4066class Sub(Binary):
4067    pass
4068
4069
4070class ArrayOverlaps(Binary):
4071    pass
4072
4073
4074# Unary Expressions
4075# (NOT a)
4076class Unary(Condition):
4077    pass
4078
4079
4080class BitwiseNot(Unary):
4081    pass
4082
4083
4084class Not(Unary):
4085    pass
4086
4087
4088class Paren(Unary):
4089    arg_types = {"this": True, "with": False}
4090
4091    @property
4092    def output_name(self) -> str:
4093        return self.this.name
4094
4095
4096class Neg(Unary):
4097    pass
4098
4099
4100class Alias(Expression):
4101    arg_types = {"this": True, "alias": False}
4102
4103    @property
4104    def output_name(self) -> str:
4105        return self.alias
4106
4107
4108class Aliases(Expression):
4109    arg_types = {"this": True, "expressions": True}
4110
4111    @property
4112    def aliases(self):
4113        return self.expressions
4114
4115
4116# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4117class AtIndex(Expression):
4118    arg_types = {"this": True, "expression": True}
4119
4120
4121class AtTimeZone(Expression):
4122    arg_types = {"this": True, "zone": True}
4123
4124
4125class Between(Predicate):
4126    arg_types = {"this": True, "low": True, "high": True}
4127
4128
4129class Bracket(Condition):
4130    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4131    arg_types = {"this": True, "expressions": True, "offset": False, "safe": False}
4132
4133    @property
4134    def output_name(self) -> str:
4135        if len(self.expressions) == 1:
4136            return self.expressions[0].output_name
4137
4138        return super().output_name
4139
4140
4141class Distinct(Expression):
4142    arg_types = {"expressions": False, "on": False}
4143
4144
4145class In(Predicate):
4146    arg_types = {
4147        "this": True,
4148        "expressions": False,
4149        "query": False,
4150        "unnest": False,
4151        "field": False,
4152        "is_global": False,
4153    }
4154
4155
4156# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4157class ForIn(Expression):
4158    arg_types = {"this": True, "expression": True}
4159
4160
4161class TimeUnit(Expression):
4162    """Automatically converts unit arg into a var."""
4163
4164    arg_types = {"unit": False}
4165
4166    UNABBREVIATED_UNIT_NAME = {
4167        "D": "DAY",
4168        "H": "HOUR",
4169        "M": "MINUTE",
4170        "MS": "MILLISECOND",
4171        "NS": "NANOSECOND",
4172        "Q": "QUARTER",
4173        "S": "SECOND",
4174        "US": "MICROSECOND",
4175        "W": "WEEK",
4176        "Y": "YEAR",
4177    }
4178
4179    VAR_LIKE = (Column, Literal, Var)
4180
4181    def __init__(self, **args):
4182        unit = args.get("unit")
4183        if isinstance(unit, self.VAR_LIKE):
4184            args["unit"] = Var(
4185                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4186            )
4187        elif isinstance(unit, Week):
4188            unit.set("this", Var(this=unit.this.name.upper()))
4189
4190        super().__init__(**args)
4191
4192    @property
4193    def unit(self) -> t.Optional[Var]:
4194        return self.args.get("unit")
4195
4196
4197class IntervalOp(TimeUnit):
4198    arg_types = {"unit": True, "expression": True}
4199
4200    def interval(self):
4201        return Interval(
4202            this=self.expression.copy(),
4203            unit=self.unit.copy(),
4204        )
4205
4206
4207# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4208# https://trino.io/docs/current/language/types.html#interval-day-to-second
4209# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4210class IntervalSpan(DataType):
4211    arg_types = {"this": True, "expression": True}
4212
4213
4214class Interval(TimeUnit):
4215    arg_types = {"this": False, "unit": False}
4216
4217
4218class IgnoreNulls(Expression):
4219    pass
4220
4221
4222class RespectNulls(Expression):
4223    pass
4224
4225
4226# Functions
4227class Func(Condition):
4228    """
4229    The base class for all function expressions.
4230
4231    Attributes:
4232        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4233            treated as a variable length argument and the argument's value will be stored as a list.
4234        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4235            for this function expression. These values are used to map this node to a name during parsing
4236            as well as to provide the function's name during SQL string generation. By default the SQL
4237            name is set to the expression's class name transformed to snake case.
4238    """
4239
4240    is_var_len_args = False
4241
4242    @classmethod
4243    def from_arg_list(cls, args):
4244        if cls.is_var_len_args:
4245            all_arg_keys = list(cls.arg_types)
4246            # If this function supports variable length argument treat the last argument as such.
4247            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4248            num_non_var = len(non_var_len_arg_keys)
4249
4250            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4251            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4252        else:
4253            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4254
4255        return cls(**args_dict)
4256
4257    @classmethod
4258    def sql_names(cls):
4259        if cls is Func:
4260            raise NotImplementedError(
4261                "SQL name is only supported by concrete function implementations"
4262            )
4263        if "_sql_names" not in cls.__dict__:
4264            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4265        return cls._sql_names
4266
4267    @classmethod
4268    def sql_name(cls):
4269        return cls.sql_names()[0]
4270
4271    @classmethod
4272    def default_parser_mappings(cls):
4273        return {name: cls.from_arg_list for name in cls.sql_names()}
4274
4275
4276class AggFunc(Func):
4277    pass
4278
4279
4280class ParameterizedAgg(AggFunc):
4281    arg_types = {"this": True, "expressions": True, "params": True}
4282
4283
4284class Abs(Func):
4285    pass
4286
4287
4288class ArgMax(AggFunc):
4289    arg_types = {"this": True, "expression": True, "count": False}
4290    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4291
4292
4293class ArgMin(AggFunc):
4294    arg_types = {"this": True, "expression": True, "count": False}
4295    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4296
4297
4298class ApproxTopK(AggFunc):
4299    arg_types = {"this": True, "expression": False, "counters": False}
4300
4301
4302class Flatten(Func):
4303    pass
4304
4305
4306# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4307class Transform(Func):
4308    arg_types = {"this": True, "expression": True}
4309
4310
4311class Anonymous(Func):
4312    arg_types = {"this": True, "expressions": False}
4313    is_var_len_args = True
4314
4315
4316class AnonymousAggFunc(AggFunc):
4317    arg_types = {"this": True, "expressions": False}
4318    is_var_len_args = True
4319
4320
4321# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4322class CombinedAggFunc(AnonymousAggFunc):
4323    arg_types = {"this": True, "expressions": False, "parts": True}
4324
4325
4326class CombinedParameterizedAgg(ParameterizedAgg):
4327    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4328
4329
4330# https://docs.snowflake.com/en/sql-reference/functions/hll
4331# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4332class Hll(AggFunc):
4333    arg_types = {"this": True, "expressions": False}
4334    is_var_len_args = True
4335
4336
4337class ApproxDistinct(AggFunc):
4338    arg_types = {"this": True, "accuracy": False}
4339    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4340
4341
4342class Array(Func):
4343    arg_types = {"expressions": False}
4344    is_var_len_args = True
4345
4346
4347# https://docs.snowflake.com/en/sql-reference/functions/to_array
4348class ToArray(Func):
4349    pass
4350
4351
4352# https://docs.snowflake.com/en/sql-reference/functions/to_char
4353# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4354class ToChar(Func):
4355    arg_types = {"this": True, "format": False, "nlsparam": False}
4356
4357
4358class GenerateSeries(Func):
4359    arg_types = {"start": True, "end": True, "step": False}
4360
4361
4362class ArrayAgg(AggFunc):
4363    pass
4364
4365
4366class ArrayUniqueAgg(AggFunc):
4367    pass
4368
4369
4370class ArrayAll(Func):
4371    arg_types = {"this": True, "expression": True}
4372
4373
4374class ArrayAny(Func):
4375    arg_types = {"this": True, "expression": True}
4376
4377
4378class ArrayConcat(Func):
4379    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4380    arg_types = {"this": True, "expressions": False}
4381    is_var_len_args = True
4382
4383
4384class ArrayContains(Binary, Func):
4385    pass
4386
4387
4388class ArrayContained(Binary):
4389    pass
4390
4391
4392class ArrayFilter(Func):
4393    arg_types = {"this": True, "expression": True}
4394    _sql_names = ["FILTER", "ARRAY_FILTER"]
4395
4396
4397class ArrayJoin(Func):
4398    arg_types = {"this": True, "expression": True, "null": False}
4399
4400
4401class ArraySize(Func):
4402    arg_types = {"this": True, "expression": False}
4403
4404
4405class ArraySort(Func):
4406    arg_types = {"this": True, "expression": False}
4407
4408
4409class ArraySum(Func):
4410    pass
4411
4412
4413class ArrayUnionAgg(AggFunc):
4414    pass
4415
4416
4417class Avg(AggFunc):
4418    pass
4419
4420
4421class AnyValue(AggFunc):
4422    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4423
4424
4425class First(Func):
4426    arg_types = {"this": True, "ignore_nulls": False}
4427
4428
4429class Last(Func):
4430    arg_types = {"this": True, "ignore_nulls": False}
4431
4432
4433class Case(Func):
4434    arg_types = {"this": False, "ifs": True, "default": False}
4435
4436    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4437        instance = maybe_copy(self, copy)
4438        instance.append(
4439            "ifs",
4440            If(
4441                this=maybe_parse(condition, copy=copy, **opts),
4442                true=maybe_parse(then, copy=copy, **opts),
4443            ),
4444        )
4445        return instance
4446
4447    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4448        instance = maybe_copy(self, copy)
4449        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4450        return instance
4451
4452
4453class Cast(Func):
4454    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4455
4456    @property
4457    def name(self) -> str:
4458        return self.this.name
4459
4460    @property
4461    def to(self) -> DataType:
4462        return self.args["to"]
4463
4464    @property
4465    def output_name(self) -> str:
4466        return self.name
4467
4468    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4469        """
4470        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4471        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4472        array<int> != array<float>.
4473
4474        Args:
4475            dtypes: the data types to compare this Cast's DataType to.
4476
4477        Returns:
4478            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4479        """
4480        return self.to.is_type(*dtypes)
4481
4482
4483class TryCast(Cast):
4484    pass
4485
4486
4487class CastToStrType(Func):
4488    arg_types = {"this": True, "to": True}
4489
4490
4491class Collate(Binary, Func):
4492    pass
4493
4494
4495class Ceil(Func):
4496    arg_types = {"this": True, "decimals": False}
4497    _sql_names = ["CEIL", "CEILING"]
4498
4499
4500class Coalesce(Func):
4501    arg_types = {"this": True, "expressions": False}
4502    is_var_len_args = True
4503    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4504
4505
4506class Chr(Func):
4507    arg_types = {"this": True, "charset": False, "expressions": False}
4508    is_var_len_args = True
4509    _sql_names = ["CHR", "CHAR"]
4510
4511
4512class Concat(Func):
4513    arg_types = {"expressions": True, "safe": False, "coalesce": False}
4514    is_var_len_args = True
4515
4516
4517class ConcatWs(Concat):
4518    _sql_names = ["CONCAT_WS"]
4519
4520
4521class Count(AggFunc):
4522    arg_types = {"this": False, "expressions": False}
4523    is_var_len_args = True
4524
4525
4526class CountIf(AggFunc):
4527    _sql_names = ["COUNT_IF", "COUNTIF"]
4528
4529
4530class CurrentDate(Func):
4531    arg_types = {"this": False}
4532
4533
4534class CurrentDatetime(Func):
4535    arg_types = {"this": False}
4536
4537
4538class CurrentTime(Func):
4539    arg_types = {"this": False}
4540
4541
4542class CurrentTimestamp(Func):
4543    arg_types = {"this": False}
4544
4545
4546class CurrentUser(Func):
4547    arg_types = {"this": False}
4548
4549
4550class DateAdd(Func, IntervalOp):
4551    arg_types = {"this": True, "expression": True, "unit": False}
4552
4553
4554class DateSub(Func, IntervalOp):
4555    arg_types = {"this": True, "expression": True, "unit": False}
4556
4557
4558class DateDiff(Func, TimeUnit):
4559    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4560    arg_types = {"this": True, "expression": True, "unit": False}
4561
4562
4563class DateTrunc(Func):
4564    arg_types = {"unit": True, "this": True, "zone": False}
4565
4566    def __init__(self, **args):
4567        unit = args.get("unit")
4568        if isinstance(unit, TimeUnit.VAR_LIKE):
4569            args["unit"] = Literal.string(
4570                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4571            )
4572        elif isinstance(unit, Week):
4573            unit.set("this", Literal.string(unit.this.name.upper()))
4574
4575        super().__init__(**args)
4576
4577    @property
4578    def unit(self) -> Expression:
4579        return self.args["unit"]
4580
4581
4582class DatetimeAdd(Func, IntervalOp):
4583    arg_types = {"this": True, "expression": True, "unit": False}
4584
4585
4586class DatetimeSub(Func, IntervalOp):
4587    arg_types = {"this": True, "expression": True, "unit": False}
4588
4589
4590class DatetimeDiff(Func, TimeUnit):
4591    arg_types = {"this": True, "expression": True, "unit": False}
4592
4593
4594class DatetimeTrunc(Func, TimeUnit):
4595    arg_types = {"this": True, "unit": True, "zone": False}
4596
4597
4598class DayOfWeek(Func):
4599    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4600
4601
4602class DayOfMonth(Func):
4603    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4604
4605
4606class DayOfYear(Func):
4607    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4608
4609
4610class ToDays(Func):
4611    pass
4612
4613
4614class WeekOfYear(Func):
4615    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4616
4617
4618class MonthsBetween(Func):
4619    arg_types = {"this": True, "expression": True, "roundoff": False}
4620
4621
4622class LastDay(Func, TimeUnit):
4623    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
4624    arg_types = {"this": True, "unit": False}
4625
4626
4627class Extract(Func):
4628    arg_types = {"this": True, "expression": True}
4629
4630
4631class Timestamp(Func):
4632    arg_types = {"this": False, "expression": False}
4633
4634
4635class TimestampAdd(Func, TimeUnit):
4636    arg_types = {"this": True, "expression": True, "unit": False}
4637
4638
4639class TimestampSub(Func, TimeUnit):
4640    arg_types = {"this": True, "expression": True, "unit": False}
4641
4642
4643class TimestampDiff(Func, TimeUnit):
4644    arg_types = {"this": True, "expression": True, "unit": False}
4645
4646
4647class TimestampTrunc(Func, TimeUnit):
4648    arg_types = {"this": True, "unit": True, "zone": False}
4649
4650
4651class TimeAdd(Func, TimeUnit):
4652    arg_types = {"this": True, "expression": True, "unit": False}
4653
4654
4655class TimeSub(Func, TimeUnit):
4656    arg_types = {"this": True, "expression": True, "unit": False}
4657
4658
4659class TimeDiff(Func, TimeUnit):
4660    arg_types = {"this": True, "expression": True, "unit": False}
4661
4662
4663class TimeTrunc(Func, TimeUnit):
4664    arg_types = {"this": True, "unit": True, "zone": False}
4665
4666
4667class DateFromParts(Func):
4668    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
4669    arg_types = {"year": True, "month": True, "day": True}
4670
4671
4672class TimeFromParts(Func):
4673    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
4674    arg_types = {
4675        "hour": True,
4676        "min": True,
4677        "sec": True,
4678        "nano": False,
4679        "fractions": False,
4680        "precision": False,
4681    }
4682
4683
4684class DateStrToDate(Func):
4685    pass
4686
4687
4688class DateToDateStr(Func):
4689    pass
4690
4691
4692class DateToDi(Func):
4693    pass
4694
4695
4696# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4697class Date(Func):
4698    arg_types = {"this": False, "zone": False, "expressions": False}
4699    is_var_len_args = True
4700
4701
4702class Day(Func):
4703    pass
4704
4705
4706class Decode(Func):
4707    arg_types = {"this": True, "charset": True, "replace": False}
4708
4709
4710class DiToDate(Func):
4711    pass
4712
4713
4714class Encode(Func):
4715    arg_types = {"this": True, "charset": True}
4716
4717
4718class Exp(Func):
4719    pass
4720
4721
4722# https://docs.snowflake.com/en/sql-reference/functions/flatten
4723class Explode(Func):
4724    arg_types = {"this": True, "expressions": False}
4725    is_var_len_args = True
4726
4727
4728class ExplodeOuter(Explode):
4729    pass
4730
4731
4732class Posexplode(Explode):
4733    pass
4734
4735
4736class PosexplodeOuter(Posexplode):
4737    pass
4738
4739
4740class Floor(Func):
4741    arg_types = {"this": True, "decimals": False}
4742
4743
4744class FromBase64(Func):
4745    pass
4746
4747
4748class ToBase64(Func):
4749    pass
4750
4751
4752class Greatest(Func):
4753    arg_types = {"this": True, "expressions": False}
4754    is_var_len_args = True
4755
4756
4757class GroupConcat(AggFunc):
4758    arg_types = {"this": True, "separator": False}
4759
4760
4761class Hex(Func):
4762    pass
4763
4764
4765class Xor(Connector, Func):
4766    arg_types = {"this": False, "expression": False, "expressions": False}
4767
4768
4769class If(Func):
4770    arg_types = {"this": True, "true": True, "false": False}
4771
4772
4773class Nullif(Func):
4774    arg_types = {"this": True, "expression": True}
4775
4776
4777class Initcap(Func):
4778    arg_types = {"this": True, "expression": False}
4779
4780
4781class IsNan(Func):
4782    _sql_names = ["IS_NAN", "ISNAN"]
4783
4784
4785class IsInf(Func):
4786    _sql_names = ["IS_INF", "ISINF"]
4787
4788
4789class FormatJson(Expression):
4790    pass
4791
4792
4793class JSONKeyValue(Expression):
4794    arg_types = {"this": True, "expression": True}
4795
4796
4797class JSONObject(Func):
4798    arg_types = {
4799        "expressions": False,
4800        "null_handling": False,
4801        "unique_keys": False,
4802        "return_type": False,
4803        "encoding": False,
4804    }
4805
4806
4807# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4808class JSONArray(Func):
4809    arg_types = {
4810        "expressions": True,
4811        "null_handling": False,
4812        "return_type": False,
4813        "strict": False,
4814    }
4815
4816
4817# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4818class JSONArrayAgg(Func):
4819    arg_types = {
4820        "this": True,
4821        "order": False,
4822        "null_handling": False,
4823        "return_type": False,
4824        "strict": False,
4825    }
4826
4827
4828# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4829# Note: parsing of JSON column definitions is currently incomplete.
4830class JSONColumnDef(Expression):
4831    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4832
4833
4834class JSONSchema(Expression):
4835    arg_types = {"expressions": True}
4836
4837
4838# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4839class JSONTable(Func):
4840    arg_types = {
4841        "this": True,
4842        "schema": True,
4843        "path": False,
4844        "error_handling": False,
4845        "empty_handling": False,
4846    }
4847
4848
4849class OpenJSONColumnDef(Expression):
4850    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4851
4852
4853class OpenJSON(Func):
4854    arg_types = {"this": True, "path": False, "expressions": False}
4855
4856
4857class JSONBContains(Binary):
4858    _sql_names = ["JSONB_CONTAINS"]
4859
4860
4861class JSONExtract(Binary, Func):
4862    _sql_names = ["JSON_EXTRACT"]
4863
4864
4865class JSONExtractScalar(JSONExtract):
4866    _sql_names = ["JSON_EXTRACT_SCALAR"]
4867
4868
4869class JSONBExtract(JSONExtract):
4870    _sql_names = ["JSONB_EXTRACT"]
4871
4872
4873class JSONBExtractScalar(JSONExtract):
4874    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4875
4876
4877class JSONFormat(Func):
4878    arg_types = {"this": False, "options": False}
4879    _sql_names = ["JSON_FORMAT"]
4880
4881
4882# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4883class JSONArrayContains(Binary, Predicate, Func):
4884    _sql_names = ["JSON_ARRAY_CONTAINS"]
4885
4886
4887class ParseJSON(Func):
4888    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4889    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4890    arg_types = {"this": True, "expressions": False}
4891    is_var_len_args = True
4892
4893
4894# https://docs.snowflake.com/en/sql-reference/functions/get_path
4895class GetPath(Func):
4896    arg_types = {"this": True, "expression": True}
4897
4898    @property
4899    def output_name(self) -> str:
4900        return self.expression.output_name
4901
4902
4903class Least(Func):
4904    arg_types = {"this": True, "expressions": False}
4905    is_var_len_args = True
4906
4907
4908class Left(Func):
4909    arg_types = {"this": True, "expression": True}
4910
4911
4912class Right(Func):
4913    arg_types = {"this": True, "expression": True}
4914
4915
4916class Length(Func):
4917    _sql_names = ["LENGTH", "LEN"]
4918
4919
4920class Levenshtein(Func):
4921    arg_types = {
4922        "this": True,
4923        "expression": False,
4924        "ins_cost": False,
4925        "del_cost": False,
4926        "sub_cost": False,
4927    }
4928
4929
4930class Ln(Func):
4931    pass
4932
4933
4934class Log(Func):
4935    arg_types = {"this": True, "expression": False}
4936
4937
4938class Log2(Func):
4939    pass
4940
4941
4942class Log10(Func):
4943    pass
4944
4945
4946class LogicalOr(AggFunc):
4947    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4948
4949
4950class LogicalAnd(AggFunc):
4951    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4952
4953
4954class Lower(Func):
4955    _sql_names = ["LOWER", "LCASE"]
4956
4957
4958class Map(Func):
4959    arg_types = {"keys": False, "values": False}
4960
4961    @property
4962    def keys(self) -> t.List[Expression]:
4963        keys = self.args.get("keys")
4964        return keys.expressions if keys else []
4965
4966    @property
4967    def values(self) -> t.List[Expression]:
4968        values = self.args.get("values")
4969        return values.expressions if values else []
4970
4971
4972class MapFromEntries(Func):
4973    pass
4974
4975
4976class StarMap(Func):
4977    pass
4978
4979
4980class VarMap(Func):
4981    arg_types = {"keys": True, "values": True}
4982    is_var_len_args = True
4983
4984    @property
4985    def keys(self) -> t.List[Expression]:
4986        return self.args["keys"].expressions
4987
4988    @property
4989    def values(self) -> t.List[Expression]:
4990        return self.args["values"].expressions
4991
4992
4993# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4994class MatchAgainst(Func):
4995    arg_types = {"this": True, "expressions": True, "modifier": False}
4996
4997
4998class Max(AggFunc):
4999    arg_types = {"this": True, "expressions": False}
5000    is_var_len_args = True
5001
5002
5003class MD5(Func):
5004    _sql_names = ["MD5"]
5005
5006
5007# Represents the variant of the MD5 function that returns a binary value
5008class MD5Digest(Func):
5009    _sql_names = ["MD5_DIGEST"]
5010
5011
5012class Min(AggFunc):
5013    arg_types = {"this": True, "expressions": False}
5014    is_var_len_args = True
5015
5016
5017class Month(Func):
5018    pass
5019
5020
5021class Nvl2(Func):
5022    arg_types = {"this": True, "true": True, "false": False}
5023
5024
5025# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5026class Predict(Func):
5027    arg_types = {"this": True, "expression": True, "params_struct": False}
5028
5029
5030class Pow(Binary, Func):
5031    _sql_names = ["POWER", "POW"]
5032
5033
5034class PercentileCont(AggFunc):
5035    arg_types = {"this": True, "expression": False}
5036
5037
5038class PercentileDisc(AggFunc):
5039    arg_types = {"this": True, "expression": False}
5040
5041
5042class Quantile(AggFunc):
5043    arg_types = {"this": True, "quantile": True}
5044
5045
5046class ApproxQuantile(Quantile):
5047    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5048
5049
5050class Rand(Func):
5051    _sql_names = ["RAND", "RANDOM"]
5052    arg_types = {"this": False}
5053
5054
5055class Randn(Func):
5056    arg_types = {"this": False}
5057
5058
5059class RangeN(Func):
5060    arg_types = {"this": True, "expressions": True, "each": False}
5061
5062
5063class ReadCSV(Func):
5064    _sql_names = ["READ_CSV"]
5065    is_var_len_args = True
5066    arg_types = {"this": True, "expressions": False}
5067
5068
5069class Reduce(Func):
5070    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5071
5072
5073class RegexpExtract(Func):
5074    arg_types = {
5075        "this": True,
5076        "expression": True,
5077        "position": False,
5078        "occurrence": False,
5079        "parameters": False,
5080        "group": False,
5081    }
5082
5083
5084class RegexpReplace(Func):
5085    arg_types = {
5086        "this": True,
5087        "expression": True,
5088        "replacement": True,
5089        "position": False,
5090        "occurrence": False,
5091        "parameters": False,
5092        "modifiers": False,
5093    }
5094
5095
5096class RegexpLike(Binary, Func):
5097    arg_types = {"this": True, "expression": True, "flag": False}
5098
5099
5100class RegexpILike(Binary, Func):
5101    arg_types = {"this": True, "expression": True, "flag": False}
5102
5103
5104# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5105# limit is the number of times a pattern is applied
5106class RegexpSplit(Func):
5107    arg_types = {"this": True, "expression": True, "limit": False}
5108
5109
5110class Repeat(Func):
5111    arg_types = {"this": True, "times": True}
5112
5113
5114class Round(Func):
5115    arg_types = {"this": True, "decimals": False}
5116
5117
5118class RowNumber(Func):
5119    arg_types: t.Dict[str, t.Any] = {}
5120
5121
5122class SafeDivide(Func):
5123    arg_types = {"this": True, "expression": True}
5124
5125
5126class SHA(Func):
5127    _sql_names = ["SHA", "SHA1"]
5128
5129
5130class SHA2(Func):
5131    _sql_names = ["SHA2"]
5132    arg_types = {"this": True, "length": False}
5133
5134
5135class SortArray(Func):
5136    arg_types = {"this": True, "asc": False}
5137
5138
5139class Split(Func):
5140    arg_types = {"this": True, "expression": True, "limit": False}
5141
5142
5143# Start may be omitted in the case of postgres
5144# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5145class Substring(Func):
5146    arg_types = {"this": True, "start": False, "length": False}
5147
5148
5149class StandardHash(Func):
5150    arg_types = {"this": True, "expression": False}
5151
5152
5153class StartsWith(Func):
5154    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5155    arg_types = {"this": True, "expression": True}
5156
5157
5158class StrPosition(Func):
5159    arg_types = {
5160        "this": True,
5161        "substr": True,
5162        "position": False,
5163        "instance": False,
5164    }
5165
5166
5167class StrToDate(Func):
5168    arg_types = {"this": True, "format": True}
5169
5170
5171class StrToTime(Func):
5172    arg_types = {"this": True, "format": True, "zone": False}
5173
5174
5175# Spark allows unix_timestamp()
5176# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5177class StrToUnix(Func):
5178    arg_types = {"this": False, "format": False}
5179
5180
5181# https://prestodb.io/docs/current/functions/string.html
5182# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5183class StrToMap(Func):
5184    arg_types = {
5185        "this": True,
5186        "pair_delim": False,
5187        "key_value_delim": False,
5188        "duplicate_resolution_callback": False,
5189    }
5190
5191
5192class NumberToStr(Func):
5193    arg_types = {"this": True, "format": True, "culture": False}
5194
5195
5196class FromBase(Func):
5197    arg_types = {"this": True, "expression": True}
5198
5199
5200class Struct(Func):
5201    arg_types = {"expressions": False}
5202    is_var_len_args = True
5203
5204
5205class StructExtract(Func):
5206    arg_types = {"this": True, "expression": True}
5207
5208
5209# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5210# https://docs.snowflake.com/en/sql-reference/functions/insert
5211class Stuff(Func):
5212    _sql_names = ["STUFF", "INSERT"]
5213    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5214
5215
5216class Sum(AggFunc):
5217    pass
5218
5219
5220class Sqrt(Func):
5221    pass
5222
5223
5224class Stddev(AggFunc):
5225    pass
5226
5227
5228class StddevPop(AggFunc):
5229    pass
5230
5231
5232class StddevSamp(AggFunc):
5233    pass
5234
5235
5236class TimeToStr(Func):
5237    arg_types = {"this": True, "format": True, "culture": False}
5238
5239
5240class TimeToTimeStr(Func):
5241    pass
5242
5243
5244class TimeToUnix(Func):
5245    pass
5246
5247
5248class TimeStrToDate(Func):
5249    pass
5250
5251
5252class TimeStrToTime(Func):
5253    pass
5254
5255
5256class TimeStrToUnix(Func):
5257    pass
5258
5259
5260class Trim(Func):
5261    arg_types = {
5262        "this": True,
5263        "expression": False,
5264        "position": False,
5265        "collation": False,
5266    }
5267
5268
5269class TsOrDsAdd(Func, TimeUnit):
5270    # return_type is used to correctly cast the arguments of this expression when transpiling it
5271    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5272
5273    @property
5274    def return_type(self) -> DataType:
5275        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
5276
5277
5278class TsOrDsDiff(Func, TimeUnit):
5279    arg_types = {"this": True, "expression": True, "unit": False}
5280
5281
5282class TsOrDsToDateStr(Func):
5283    pass
5284
5285
5286class TsOrDsToDate(Func):
5287    arg_types = {"this": True, "format": False}
5288
5289
5290class TsOrDsToTime(Func):
5291    pass
5292
5293
5294class TsOrDiToDi(Func):
5295    pass
5296
5297
5298class Unhex(Func):
5299    pass
5300
5301
5302# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
5303class UnixDate(Func):
5304    pass
5305
5306
5307class UnixToStr(Func):
5308    arg_types = {"this": True, "format": False}
5309
5310
5311# https://prestodb.io/docs/current/functions/datetime.html
5312# presto has weird zone/hours/minutes
5313class UnixToTime(Func):
5314    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5315
5316    SECONDS = Literal.string("seconds")
5317    MILLIS = Literal.string("millis")
5318    MICROS = Literal.string("micros")
5319    NANOS = Literal.string("nanos")
5320
5321
5322class UnixToTimeStr(Func):
5323    pass
5324
5325
5326class TimestampFromParts(Func):
5327    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
5328    arg_types = {
5329        "year": True,
5330        "month": True,
5331        "day": True,
5332        "hour": True,
5333        "min": True,
5334        "sec": True,
5335        "nano": False,
5336        "zone": False,
5337        "milli": False,
5338    }
5339
5340
5341class Upper(Func):
5342    _sql_names = ["UPPER", "UCASE"]
5343
5344
5345class Variance(AggFunc):
5346    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5347
5348
5349class VariancePop(AggFunc):
5350    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5351
5352
5353class Week(Func):
5354    arg_types = {"this": True, "mode": False}
5355
5356
5357class XMLTable(Func):
5358    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5359
5360
5361class Year(Func):
5362    pass
5363
5364
5365class Use(Expression):
5366    arg_types = {"this": True, "kind": False}
5367
5368
5369class Merge(Expression):
5370    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5371
5372
5373class When(Func):
5374    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5375
5376
5377# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5378# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5379class NextValueFor(Func):
5380    arg_types = {"this": True, "order": False}
5381
5382
5383def _norm_arg(arg):
5384    return arg.lower() if type(arg) is str else arg
5385
5386
5387ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5388FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
5389
5390
5391# Helpers
5392@t.overload
5393def maybe_parse(
5394    sql_or_expression: ExpOrStr,
5395    *,
5396    into: t.Type[E],
5397    dialect: DialectType = None,
5398    prefix: t.Optional[str] = None,
5399    copy: bool = False,
5400    **opts,
5401) -> E:
5402    ...
5403
5404
5405@t.overload
5406def maybe_parse(
5407    sql_or_expression: str | E,
5408    *,
5409    into: t.Optional[IntoType] = None,
5410    dialect: DialectType = None,
5411    prefix: t.Optional[str] = None,
5412    copy: bool = False,
5413    **opts,
5414) -> E:
5415    ...
5416
5417
5418def maybe_parse(
5419    sql_or_expression: ExpOrStr,
5420    *,
5421    into: t.Optional[IntoType] = None,
5422    dialect: DialectType = None,
5423    prefix: t.Optional[str] = None,
5424    copy: bool = False,
5425    **opts,
5426) -> Expression:
5427    """Gracefully handle a possible string or expression.
5428
5429    Example:
5430        >>> maybe_parse("1")
5431        Literal(this=1, is_string=False)
5432        >>> maybe_parse(to_identifier("x"))
5433        Identifier(this=x, quoted=False)
5434
5435    Args:
5436        sql_or_expression: the SQL code string or an expression
5437        into: the SQLGlot Expression to parse into
5438        dialect: the dialect used to parse the input expressions (in the case that an
5439            input expression is a SQL string).
5440        prefix: a string to prefix the sql with before it gets parsed
5441            (automatically includes a space)
5442        copy: whether or not to copy the expression.
5443        **opts: other options to use to parse the input expressions (again, in the case
5444            that an input expression is a SQL string).
5445
5446    Returns:
5447        Expression: the parsed or given expression.
5448    """
5449    if isinstance(sql_or_expression, Expression):
5450        if copy:
5451            return sql_or_expression.copy()
5452        return sql_or_expression
5453
5454    if sql_or_expression is None:
5455        raise ParseError(f"SQL cannot be None")
5456
5457    import sqlglot
5458
5459    sql = str(sql_or_expression)
5460    if prefix:
5461        sql = f"{prefix} {sql}"
5462
5463    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5464
5465
5466@t.overload
5467def maybe_copy(instance: None, copy: bool = True) -> None:
5468    ...
5469
5470
5471@t.overload
5472def maybe_copy(instance: E, copy: bool = True) -> E:
5473    ...
5474
5475
5476def maybe_copy(instance, copy=True):
5477    return instance.copy() if copy and instance else instance
5478
5479
5480def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
5481    """Generate a textual representation of an Expression tree"""
5482    indent = "\n" + ("  " * (level + 1))
5483    delim = f",{indent}"
5484
5485    if isinstance(node, Expression):
5486        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
5487
5488        if (node.type or verbose) and not isinstance(node, DataType):
5489            args["_type"] = node.type
5490        if node.comments or verbose:
5491            args["_comments"] = node.comments
5492
5493        if verbose:
5494            args["_id"] = id(node)
5495
5496        # Inline leaves for a more compact representation
5497        if node.is_leaf():
5498            indent = ""
5499            delim = ", "
5500
5501        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
5502        return f"{node.__class__.__name__}({indent}{items})"
5503
5504    if isinstance(node, list):
5505        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
5506        items = f"{indent}{items}" if items else ""
5507        return f"[{items}]"
5508
5509    # Indent multiline strings to match the current level
5510    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
5511
5512
5513def _is_wrong_expression(expression, into):
5514    return isinstance(expression, Expression) and not isinstance(expression, into)
5515
5516
5517def _apply_builder(
5518    expression,
5519    instance,
5520    arg,
5521    copy=True,
5522    prefix=None,
5523    into=None,
5524    dialect=None,
5525    into_arg="this",
5526    **opts,
5527):
5528    if _is_wrong_expression(expression, into):
5529        expression = into(**{into_arg: expression})
5530    instance = maybe_copy(instance, copy)
5531    expression = maybe_parse(
5532        sql_or_expression=expression,
5533        prefix=prefix,
5534        into=into,
5535        dialect=dialect,
5536        **opts,
5537    )
5538    instance.set(arg, expression)
5539    return instance
5540
5541
5542def _apply_child_list_builder(
5543    *expressions,
5544    instance,
5545    arg,
5546    append=True,
5547    copy=True,
5548    prefix=None,
5549    into=None,
5550    dialect=None,
5551    properties=None,
5552    **opts,
5553):
5554    instance = maybe_copy(instance, copy)
5555    parsed = []
5556    for expression in expressions:
5557        if expression is not None:
5558            if _is_wrong_expression(expression, into):
5559                expression = into(expressions=[expression])
5560
5561            expression = maybe_parse(
5562                expression,
5563                into=into,
5564                dialect=dialect,
5565                prefix=prefix,
5566                **opts,
5567            )
5568            parsed.extend(expression.expressions)
5569
5570    existing = instance.args.get(arg)
5571    if append and existing:
5572        parsed = existing.expressions + parsed
5573
5574    child = into(expressions=parsed)
5575    for k, v in (properties or {}).items():
5576        child.set(k, v)
5577    instance.set(arg, child)
5578
5579    return instance
5580
5581
5582def _apply_list_builder(
5583    *expressions,
5584    instance,
5585    arg,
5586    append=True,
5587    copy=True,
5588    prefix=None,
5589    into=None,
5590    dialect=None,
5591    **opts,
5592):
5593    inst = maybe_copy(instance, copy)
5594
5595    expressions = [
5596        maybe_parse(
5597            sql_or_expression=expression,
5598            into=into,
5599            prefix=prefix,
5600            dialect=dialect,
5601            **opts,
5602        )
5603        for expression in expressions
5604        if expression is not None
5605    ]
5606
5607    existing_expressions = inst.args.get(arg)
5608    if append and existing_expressions:
5609        expressions = existing_expressions + expressions
5610
5611    inst.set(arg, expressions)
5612    return inst
5613
5614
5615def _apply_conjunction_builder(
5616    *expressions,
5617    instance,
5618    arg,
5619    into=None,
5620    append=True,
5621    copy=True,
5622    dialect=None,
5623    **opts,
5624):
5625    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5626    if not expressions:
5627        return instance
5628
5629    inst = maybe_copy(instance, copy)
5630
5631    existing = inst.args.get(arg)
5632    if append and existing is not None:
5633        expressions = [existing.this if into else existing] + list(expressions)
5634
5635    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5636
5637    inst.set(arg, into(this=node) if into else node)
5638    return inst
5639
5640
5641def _apply_cte_builder(
5642    instance: E,
5643    alias: ExpOrStr,
5644    as_: ExpOrStr,
5645    recursive: t.Optional[bool] = None,
5646    append: bool = True,
5647    dialect: DialectType = None,
5648    copy: bool = True,
5649    **opts,
5650) -> E:
5651    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5652    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5653    cte = CTE(this=as_expression, alias=alias_expression)
5654    return _apply_child_list_builder(
5655        cte,
5656        instance=instance,
5657        arg="with",
5658        append=append,
5659        copy=copy,
5660        into=With,
5661        properties={"recursive": recursive or False},
5662    )
5663
5664
5665def _combine(
5666    expressions: t.Sequence[t.Optional[ExpOrStr]],
5667    operator: t.Type[Connector],
5668    dialect: DialectType = None,
5669    copy: bool = True,
5670    **opts,
5671) -> Expression:
5672    conditions = [
5673        condition(expression, dialect=dialect, copy=copy, **opts)
5674        for expression in expressions
5675        if expression is not None
5676    ]
5677
5678    this, *rest = conditions
5679    if rest:
5680        this = _wrap(this, Connector)
5681    for expression in rest:
5682        this = operator(this=this, expression=_wrap(expression, Connector))
5683
5684    return this
5685
5686
5687def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5688    return Paren(this=expression) if isinstance(expression, kind) else expression
5689
5690
5691def union(
5692    left: ExpOrStr,
5693    right: ExpOrStr,
5694    distinct: bool = True,
5695    dialect: DialectType = None,
5696    copy: bool = True,
5697    **opts,
5698) -> Union:
5699    """
5700    Initializes a syntax tree from one UNION expression.
5701
5702    Example:
5703        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5704        'SELECT * FROM foo UNION SELECT * FROM bla'
5705
5706    Args:
5707        left: the SQL code string corresponding to the left-hand side.
5708            If an `Expression` instance is passed, it will be used as-is.
5709        right: the SQL code string corresponding to the right-hand side.
5710            If an `Expression` instance is passed, it will be used as-is.
5711        distinct: set the DISTINCT flag if and only if this is true.
5712        dialect: the dialect used to parse the input expression.
5713        copy: whether or not to copy the expression.
5714        opts: other options to use to parse the input expressions.
5715
5716    Returns:
5717        The new Union instance.
5718    """
5719    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5720    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5721
5722    return Union(this=left, expression=right, distinct=distinct)
5723
5724
5725def intersect(
5726    left: ExpOrStr,
5727    right: ExpOrStr,
5728    distinct: bool = True,
5729    dialect: DialectType = None,
5730    copy: bool = True,
5731    **opts,
5732) -> Intersect:
5733    """
5734    Initializes a syntax tree from one INTERSECT expression.
5735
5736    Example:
5737        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5738        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5739
5740    Args:
5741        left: the SQL code string corresponding to the left-hand side.
5742            If an `Expression` instance is passed, it will be used as-is.
5743        right: the SQL code string corresponding to the right-hand side.
5744            If an `Expression` instance is passed, it will be used as-is.
5745        distinct: set the DISTINCT flag if and only if this is true.
5746        dialect: the dialect used to parse the input expression.
5747        copy: whether or not to copy the expression.
5748        opts: other options to use to parse the input expressions.
5749
5750    Returns:
5751        The new Intersect instance.
5752    """
5753    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5754    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5755
5756    return Intersect(this=left, expression=right, distinct=distinct)
5757
5758
5759def except_(
5760    left: ExpOrStr,
5761    right: ExpOrStr,
5762    distinct: bool = True,
5763    dialect: DialectType = None,
5764    copy: bool = True,
5765    **opts,
5766) -> Except:
5767    """
5768    Initializes a syntax tree from one EXCEPT expression.
5769
5770    Example:
5771        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5772        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5773
5774    Args:
5775        left: the SQL code string corresponding to the left-hand side.
5776            If an `Expression` instance is passed, it will be used as-is.
5777        right: the SQL code string corresponding to the right-hand side.
5778            If an `Expression` instance is passed, it will be used as-is.
5779        distinct: set the DISTINCT flag if and only if this is true.
5780        dialect: the dialect used to parse the input expression.
5781        copy: whether or not to copy the expression.
5782        opts: other options to use to parse the input expressions.
5783
5784    Returns:
5785        The new Except instance.
5786    """
5787    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5788    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5789
5790    return Except(this=left, expression=right, distinct=distinct)
5791
5792
5793def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5794    """
5795    Initializes a syntax tree from one or multiple SELECT expressions.
5796
5797    Example:
5798        >>> select("col1", "col2").from_("tbl").sql()
5799        'SELECT col1, col2 FROM tbl'
5800
5801    Args:
5802        *expressions: the SQL code string to parse as the expressions of a
5803            SELECT statement. If an Expression instance is passed, this is used as-is.
5804        dialect: the dialect used to parse the input expressions (in the case that an
5805            input expression is a SQL string).
5806        **opts: other options to use to parse the input expressions (again, in the case
5807            that an input expression is a SQL string).
5808
5809    Returns:
5810        Select: the syntax tree for the SELECT statement.
5811    """
5812    return Select().select(*expressions, dialect=dialect, **opts)
5813
5814
5815def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5816    """
5817    Initializes a syntax tree from a FROM expression.
5818
5819    Example:
5820        >>> from_("tbl").select("col1", "col2").sql()
5821        'SELECT col1, col2 FROM tbl'
5822
5823    Args:
5824        *expression: the SQL code string to parse as the FROM expressions of a
5825            SELECT statement. If an Expression instance is passed, this is used as-is.
5826        dialect: the dialect used to parse the input expression (in the case that the
5827            input expression is a SQL string).
5828        **opts: other options to use to parse the input expressions (again, in the case
5829            that the input expression is a SQL string).
5830
5831    Returns:
5832        Select: the syntax tree for the SELECT statement.
5833    """
5834    return Select().from_(expression, dialect=dialect, **opts)
5835
5836
5837def update(
5838    table: str | Table,
5839    properties: dict,
5840    where: t.Optional[ExpOrStr] = None,
5841    from_: t.Optional[ExpOrStr] = None,
5842    dialect: DialectType = None,
5843    **opts,
5844) -> Update:
5845    """
5846    Creates an update statement.
5847
5848    Example:
5849        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5850        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5851
5852    Args:
5853        *properties: dictionary of properties to set which are
5854            auto converted to sql objects eg None -> NULL
5855        where: sql conditional parsed into a WHERE statement
5856        from_: sql statement parsed into a FROM statement
5857        dialect: the dialect used to parse the input expressions.
5858        **opts: other options to use to parse the input expressions.
5859
5860    Returns:
5861        Update: the syntax tree for the UPDATE statement.
5862    """
5863    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5864    update_expr.set(
5865        "expressions",
5866        [
5867            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5868            for k, v in properties.items()
5869        ],
5870    )
5871    if from_:
5872        update_expr.set(
5873            "from",
5874            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5875        )
5876    if isinstance(where, Condition):
5877        where = Where(this=where)
5878    if where:
5879        update_expr.set(
5880            "where",
5881            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5882        )
5883    return update_expr
5884
5885
5886def delete(
5887    table: ExpOrStr,
5888    where: t.Optional[ExpOrStr] = None,
5889    returning: t.Optional[ExpOrStr] = None,
5890    dialect: DialectType = None,
5891    **opts,
5892) -> Delete:
5893    """
5894    Builds a delete statement.
5895
5896    Example:
5897        >>> delete("my_table", where="id > 1").sql()
5898        'DELETE FROM my_table WHERE id > 1'
5899
5900    Args:
5901        where: sql conditional parsed into a WHERE statement
5902        returning: sql conditional parsed into a RETURNING statement
5903        dialect: the dialect used to parse the input expressions.
5904        **opts: other options to use to parse the input expressions.
5905
5906    Returns:
5907        Delete: the syntax tree for the DELETE statement.
5908    """
5909    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5910    if where:
5911        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5912    if returning:
5913        delete_expr = t.cast(
5914            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5915        )
5916    return delete_expr
5917
5918
5919def insert(
5920    expression: ExpOrStr,
5921    into: ExpOrStr,
5922    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5923    overwrite: t.Optional[bool] = None,
5924    returning: t.Optional[ExpOrStr] = None,
5925    dialect: DialectType = None,
5926    copy: bool = True,
5927    **opts,
5928) -> Insert:
5929    """
5930    Builds an INSERT statement.
5931
5932    Example:
5933        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5934        'INSERT INTO tbl VALUES (1, 2, 3)'
5935
5936    Args:
5937        expression: the sql string or expression of the INSERT statement
5938        into: the tbl to insert data to.
5939        columns: optionally the table's column names.
5940        overwrite: whether to INSERT OVERWRITE or not.
5941        returning: sql conditional parsed into a RETURNING statement
5942        dialect: the dialect used to parse the input expressions.
5943        copy: whether or not to copy the expression.
5944        **opts: other options to use to parse the input expressions.
5945
5946    Returns:
5947        Insert: the syntax tree for the INSERT statement.
5948    """
5949    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5950    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5951
5952    if columns:
5953        this = _apply_list_builder(
5954            *columns,
5955            instance=Schema(this=this),
5956            arg="expressions",
5957            into=Identifier,
5958            copy=False,
5959            dialect=dialect,
5960            **opts,
5961        )
5962
5963    insert = Insert(this=this, expression=expr, overwrite=overwrite)
5964
5965    if returning:
5966        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
5967
5968    return insert
5969
5970
5971def condition(
5972    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5973) -> Condition:
5974    """
5975    Initialize a logical condition expression.
5976
5977    Example:
5978        >>> condition("x=1").sql()
5979        'x = 1'
5980
5981        This is helpful for composing larger logical syntax trees:
5982        >>> where = condition("x=1")
5983        >>> where = where.and_("y=1")
5984        >>> Select().from_("tbl").select("*").where(where).sql()
5985        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5986
5987    Args:
5988        *expression: the SQL code string to parse.
5989            If an Expression instance is passed, this is used as-is.
5990        dialect: the dialect used to parse the input expression (in the case that the
5991            input expression is a SQL string).
5992        copy: Whether or not to copy `expression` (only applies to expressions).
5993        **opts: other options to use to parse the input expressions (again, in the case
5994            that the input expression is a SQL string).
5995
5996    Returns:
5997        The new Condition instance
5998    """
5999    return maybe_parse(
6000        expression,
6001        into=Condition,
6002        dialect=dialect,
6003        copy=copy,
6004        **opts,
6005    )
6006
6007
6008def and_(
6009    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6010) -> Condition:
6011    """
6012    Combine multiple conditions with an AND logical operator.
6013
6014    Example:
6015        >>> and_("x=1", and_("y=1", "z=1")).sql()
6016        'x = 1 AND (y = 1 AND z = 1)'
6017
6018    Args:
6019        *expressions: the SQL code strings to parse.
6020            If an Expression instance is passed, this is used as-is.
6021        dialect: the dialect used to parse the input expression.
6022        copy: whether or not to copy `expressions` (only applies to Expressions).
6023        **opts: other options to use to parse the input expressions.
6024
6025    Returns:
6026        And: the new condition
6027    """
6028    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6029
6030
6031def or_(
6032    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6033) -> Condition:
6034    """
6035    Combine multiple conditions with an OR logical operator.
6036
6037    Example:
6038        >>> or_("x=1", or_("y=1", "z=1")).sql()
6039        'x = 1 OR (y = 1 OR z = 1)'
6040
6041    Args:
6042        *expressions: the SQL code strings to parse.
6043            If an Expression instance is passed, this is used as-is.
6044        dialect: the dialect used to parse the input expression.
6045        copy: whether or not to copy `expressions` (only applies to Expressions).
6046        **opts: other options to use to parse the input expressions.
6047
6048    Returns:
6049        Or: the new condition
6050    """
6051    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6052
6053
6054def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6055    """
6056    Wrap a condition with a NOT operator.
6057
6058    Example:
6059        >>> not_("this_suit='black'").sql()
6060        "NOT this_suit = 'black'"
6061
6062    Args:
6063        expression: the SQL code string to parse.
6064            If an Expression instance is passed, this is used as-is.
6065        dialect: the dialect used to parse the input expression.
6066        copy: whether to copy the expression or not.
6067        **opts: other options to use to parse the input expressions.
6068
6069    Returns:
6070        The new condition.
6071    """
6072    this = condition(
6073        expression,
6074        dialect=dialect,
6075        copy=copy,
6076        **opts,
6077    )
6078    return Not(this=_wrap(this, Connector))
6079
6080
6081def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6082    """
6083    Wrap an expression in parentheses.
6084
6085    Example:
6086        >>> paren("5 + 3").sql()
6087        '(5 + 3)'
6088
6089    Args:
6090        expression: the SQL code string to parse.
6091            If an Expression instance is passed, this is used as-is.
6092        copy: whether to copy the expression or not.
6093
6094    Returns:
6095        The wrapped expression.
6096    """
6097    return Paren(this=maybe_parse(expression, copy=copy))
6098
6099
6100SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
6101
6102
6103@t.overload
6104def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
6105    ...
6106
6107
6108@t.overload
6109def to_identifier(
6110    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6111) -> Identifier:
6112    ...
6113
6114
6115def to_identifier(name, quoted=None, copy=True):
6116    """Builds an identifier.
6117
6118    Args:
6119        name: The name to turn into an identifier.
6120        quoted: Whether or not force quote the identifier.
6121        copy: Whether or not to copy name if it's an Identifier.
6122
6123    Returns:
6124        The identifier ast node.
6125    """
6126
6127    if name is None:
6128        return None
6129
6130    if isinstance(name, Identifier):
6131        identifier = maybe_copy(name, copy)
6132    elif isinstance(name, str):
6133        identifier = Identifier(
6134            this=name,
6135            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6136        )
6137    else:
6138        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6139    return identifier
6140
6141
6142def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6143    """
6144    Parses a given string into an identifier.
6145
6146    Args:
6147        name: The name to parse into an identifier.
6148        dialect: The dialect to parse against.
6149
6150    Returns:
6151        The identifier ast node.
6152    """
6153    try:
6154        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6155    except ParseError:
6156        expression = to_identifier(name)
6157
6158    return expression
6159
6160
6161INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6162
6163
6164def to_interval(interval: str | Literal) -> Interval:
6165    """Builds an interval expression from a string like '1 day' or '5 months'."""
6166    if isinstance(interval, Literal):
6167        if not interval.is_string:
6168            raise ValueError("Invalid interval string.")
6169
6170        interval = interval.this
6171
6172    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6173
6174    if not interval_parts:
6175        raise ValueError("Invalid interval string.")
6176
6177    return Interval(
6178        this=Literal.string(interval_parts.group(1)),
6179        unit=Var(this=interval_parts.group(2).upper()),
6180    )
6181
6182
6183@t.overload
6184def to_table(sql_path: str | Table, **kwargs) -> Table:
6185    ...
6186
6187
6188@t.overload
6189def to_table(sql_path: None, **kwargs) -> None:
6190    ...
6191
6192
6193def to_table(
6194    sql_path: t.Optional[str | Table], dialect: DialectType = None, copy: bool = True, **kwargs
6195) -> t.Optional[Table]:
6196    """
6197    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6198    If a table is passed in then that table is returned.
6199
6200    Args:
6201        sql_path: a `[catalog].[schema].[table]` string.
6202        dialect: the source dialect according to which the table name will be parsed.
6203        copy: Whether or not to copy a table if it is passed in.
6204        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6205
6206    Returns:
6207        A table expression.
6208    """
6209    if sql_path is None or isinstance(sql_path, Table):
6210        return maybe_copy(sql_path, copy=copy)
6211    if not isinstance(sql_path, str):
6212        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6213
6214    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6215    if table:
6216        for k, v in kwargs.items():
6217            table.set(k, v)
6218
6219    return table
6220
6221
6222def to_column(sql_path: str | Column, **kwargs) -> Column:
6223    """
6224    Create a column from a `[table].[column]` sql path. Schema is optional.
6225
6226    If a column is passed in then that column is returned.
6227
6228    Args:
6229        sql_path: `[table].[column]` string
6230    Returns:
6231        Table: A column expression
6232    """
6233    if sql_path is None or isinstance(sql_path, Column):
6234        return sql_path
6235    if not isinstance(sql_path, str):
6236        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6237    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6238
6239
6240def alias_(
6241    expression: ExpOrStr,
6242    alias: str | Identifier,
6243    table: bool | t.Sequence[str | Identifier] = False,
6244    quoted: t.Optional[bool] = None,
6245    dialect: DialectType = None,
6246    copy: bool = True,
6247    **opts,
6248):
6249    """Create an Alias expression.
6250
6251    Example:
6252        >>> alias_('foo', 'bar').sql()
6253        'foo AS bar'
6254
6255        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6256        '(SELECT 1, 2) AS bar(a, b)'
6257
6258    Args:
6259        expression: the SQL code strings to parse.
6260            If an Expression instance is passed, this is used as-is.
6261        alias: the alias name to use. If the name has
6262            special characters it is quoted.
6263        table: Whether or not to create a table alias, can also be a list of columns.
6264        quoted: whether or not to quote the alias
6265        dialect: the dialect used to parse the input expression.
6266        copy: Whether or not to copy the expression.
6267        **opts: other options to use to parse the input expressions.
6268
6269    Returns:
6270        Alias: the aliased expression
6271    """
6272    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6273    alias = to_identifier(alias, quoted=quoted)
6274
6275    if table:
6276        table_alias = TableAlias(this=alias)
6277        exp.set("alias", table_alias)
6278
6279        if not isinstance(table, bool):
6280            for column in table:
6281                table_alias.append("columns", to_identifier(column, quoted=quoted))
6282
6283        return exp
6284
6285    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6286    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6287    # for the complete Window expression.
6288    #
6289    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6290
6291    if "alias" in exp.arg_types and not isinstance(exp, Window):
6292        exp.set("alias", alias)
6293        return exp
6294    return Alias(this=exp, alias=alias)
6295
6296
6297def subquery(
6298    expression: ExpOrStr,
6299    alias: t.Optional[Identifier | str] = None,
6300    dialect: DialectType = None,
6301    **opts,
6302) -> Select:
6303    """
6304    Build a subquery expression.
6305
6306    Example:
6307        >>> subquery('select x from tbl', 'bar').select('x').sql()
6308        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6309
6310    Args:
6311        expression: the SQL code strings to parse.
6312            If an Expression instance is passed, this is used as-is.
6313        alias: the alias name to use.
6314        dialect: the dialect used to parse the input expression.
6315        **opts: other options to use to parse the input expressions.
6316
6317    Returns:
6318        A new Select instance with the subquery expression included.
6319    """
6320
6321    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6322    return Select().from_(expression, dialect=dialect, **opts)
6323
6324
6325def column(
6326    col: str | Identifier,
6327    table: t.Optional[str | Identifier] = None,
6328    db: t.Optional[str | Identifier] = None,
6329    catalog: t.Optional[str | Identifier] = None,
6330    *fields: t.Union[str, Identifier],
6331    quoted: t.Optional[bool] = None,
6332    copy: bool = True,
6333) -> Column | Dot:
6334    """
6335    Build a Column.
6336
6337    Args:
6338        col: Column name.
6339        table: Table name.
6340        db: Database name.
6341        catalog: Catalog name.
6342        fields: Additional fields using dots.
6343        quoted: Whether to force quotes on the column's identifiers.
6344        copy: Whether or not to copy identifiers if passed in.
6345
6346    Returns:
6347        The new Column instance.
6348    """
6349    this: t.Union[Column, Dot] = Column(
6350        this=to_identifier(col, quoted=quoted, copy=copy),
6351        table=to_identifier(table, quoted=quoted, copy=copy),
6352        db=to_identifier(db, quoted=quoted, copy=copy),
6353        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
6354    )
6355
6356    if fields:
6357        this = Dot.build((this, *(to_identifier(field, copy=copy) for field in fields)))
6358    return this
6359
6360
6361def cast(expression: ExpOrStr, to: DATA_TYPE, **opts) -> Cast:
6362    """Cast an expression to a data type.
6363
6364    Example:
6365        >>> cast('x + 1', 'int').sql()
6366        'CAST(x + 1 AS INT)'
6367
6368    Args:
6369        expression: The expression to cast.
6370        to: The datatype to cast to.
6371
6372    Returns:
6373        The new Cast instance.
6374    """
6375    expression = maybe_parse(expression, **opts)
6376    data_type = DataType.build(to, **opts)
6377    expression = Cast(this=expression, to=data_type)
6378    expression.type = data_type
6379    return expression
6380
6381
6382def table_(
6383    table: Identifier | str,
6384    db: t.Optional[Identifier | str] = None,
6385    catalog: t.Optional[Identifier | str] = None,
6386    quoted: t.Optional[bool] = None,
6387    alias: t.Optional[Identifier | str] = None,
6388) -> Table:
6389    """Build a Table.
6390
6391    Args:
6392        table: Table name.
6393        db: Database name.
6394        catalog: Catalog name.
6395        quote: Whether to force quotes on the table's identifiers.
6396        alias: Table's alias.
6397
6398    Returns:
6399        The new Table instance.
6400    """
6401    return Table(
6402        this=to_identifier(table, quoted=quoted) if table else None,
6403        db=to_identifier(db, quoted=quoted) if db else None,
6404        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6405        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6406    )
6407
6408
6409def values(
6410    values: t.Iterable[t.Tuple[t.Any, ...]],
6411    alias: t.Optional[str] = None,
6412    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6413) -> Values:
6414    """Build VALUES statement.
6415
6416    Example:
6417        >>> values([(1, '2')]).sql()
6418        "VALUES (1, '2')"
6419
6420    Args:
6421        values: values statements that will be converted to SQL
6422        alias: optional alias
6423        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6424         If either are provided then an alias is also required.
6425
6426    Returns:
6427        Values: the Values expression object
6428    """
6429    if columns and not alias:
6430        raise ValueError("Alias is required when providing columns")
6431
6432    return Values(
6433        expressions=[convert(tup) for tup in values],
6434        alias=(
6435            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6436            if columns
6437            else (TableAlias(this=to_identifier(alias)) if alias else None)
6438        ),
6439    )
6440
6441
6442def var(name: t.Optional[ExpOrStr]) -> Var:
6443    """Build a SQL variable.
6444
6445    Example:
6446        >>> repr(var('x'))
6447        'Var(this=x)'
6448
6449        >>> repr(var(column('x', table='y')))
6450        'Var(this=x)'
6451
6452    Args:
6453        name: The name of the var or an expression who's name will become the var.
6454
6455    Returns:
6456        The new variable node.
6457    """
6458    if not name:
6459        raise ValueError("Cannot convert empty name into var.")
6460
6461    if isinstance(name, Expression):
6462        name = name.name
6463    return Var(this=name)
6464
6465
6466def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6467    """Build ALTER TABLE... RENAME... expression
6468
6469    Args:
6470        old_name: The old name of the table
6471        new_name: The new name of the table
6472
6473    Returns:
6474        Alter table expression
6475    """
6476    old_table = to_table(old_name)
6477    new_table = to_table(new_name)
6478    return AlterTable(
6479        this=old_table,
6480        actions=[
6481            RenameTable(this=new_table),
6482        ],
6483    )
6484
6485
6486def convert(value: t.Any, copy: bool = False) -> Expression:
6487    """Convert a python value into an expression object.
6488
6489    Raises an error if a conversion is not possible.
6490
6491    Args:
6492        value: A python object.
6493        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6494
6495    Returns:
6496        Expression: the equivalent expression object.
6497    """
6498    if isinstance(value, Expression):
6499        return maybe_copy(value, copy)
6500    if isinstance(value, str):
6501        return Literal.string(value)
6502    if isinstance(value, bool):
6503        return Boolean(this=value)
6504    if value is None or (isinstance(value, float) and math.isnan(value)):
6505        return NULL
6506    if isinstance(value, numbers.Number):
6507        return Literal.number(value)
6508    if isinstance(value, datetime.datetime):
6509        datetime_literal = Literal.string(
6510            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6511        )
6512        return TimeStrToTime(this=datetime_literal)
6513    if isinstance(value, datetime.date):
6514        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6515        return DateStrToDate(this=date_literal)
6516    if isinstance(value, tuple):
6517        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6518    if isinstance(value, list):
6519        return Array(expressions=[convert(v, copy=copy) for v in value])
6520    if isinstance(value, dict):
6521        return Map(
6522            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6523            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6524        )
6525    raise ValueError(f"Cannot convert {value}")
6526
6527
6528def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6529    """
6530    Replace children of an expression with the result of a lambda fun(child) -> exp.
6531    """
6532    for k, v in expression.args.items():
6533        is_list_arg = type(v) is list
6534
6535        child_nodes = v if is_list_arg else [v]
6536        new_child_nodes = []
6537
6538        for cn in child_nodes:
6539            if isinstance(cn, Expression):
6540                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6541                    new_child_nodes.append(child_node)
6542                    child_node.parent = expression
6543                    child_node.arg_key = k
6544            else:
6545                new_child_nodes.append(cn)
6546
6547        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6548
6549
6550def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6551    """
6552    Return all table names referenced through columns in an expression.
6553
6554    Example:
6555        >>> import sqlglot
6556        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6557        ['a', 'c']
6558
6559    Args:
6560        expression: expression to find table names.
6561        exclude: a table name to exclude
6562
6563    Returns:
6564        A list of unique names.
6565    """
6566    return {
6567        table
6568        for table in (column.table for column in expression.find_all(Column))
6569        if table and table != exclude
6570    }
6571
6572
6573def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
6574    """Get the full name of a table as a string.
6575
6576    Args:
6577        table: Table expression node or string.
6578        dialect: The dialect to generate the table name for.
6579        identify: Determines when an identifier should be quoted. Possible values are:
6580            False (default): Never quote, except in cases where it's mandatory by the dialect.
6581            True: Always quote.
6582
6583    Examples:
6584        >>> from sqlglot import exp, parse_one
6585        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6586        'a.b.c'
6587
6588    Returns:
6589        The table name.
6590    """
6591
6592    table = maybe_parse(table, into=Table, dialect=dialect)
6593
6594    if not table:
6595        raise ValueError(f"Cannot parse {table}")
6596
6597    return ".".join(
6598        part.sql(dialect=dialect, identify=True, copy=False)
6599        if identify or not SAFE_IDENTIFIER_RE.match(part.name)
6600        else part.name
6601        for part in table.parts
6602    )
6603
6604
6605def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
6606    """Returns a case normalized table name without quotes.
6607
6608    Args:
6609        table: the table to normalize
6610        dialect: the dialect to use for normalization rules
6611        copy: whether or not to copy the expression.
6612
6613    Examples:
6614        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
6615        'A-B.c'
6616    """
6617    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
6618
6619    return ".".join(
6620        p.name
6621        for p in normalize_identifiers(
6622            to_table(table, dialect=dialect, copy=copy), dialect=dialect
6623        ).parts
6624    )
6625
6626
6627def replace_tables(
6628    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
6629) -> E:
6630    """Replace all tables in expression according to the mapping.
6631
6632    Args:
6633        expression: expression node to be transformed and replaced.
6634        mapping: mapping of table names.
6635        dialect: the dialect of the mapping table
6636        copy: whether or not to copy the expression.
6637
6638    Examples:
6639        >>> from sqlglot import exp, parse_one
6640        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6641        'SELECT * FROM c /* a.b */'
6642
6643    Returns:
6644        The mapped expression.
6645    """
6646
6647    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
6648
6649    def _replace_tables(node: Expression) -> Expression:
6650        if isinstance(node, Table):
6651            original = normalize_table_name(node, dialect=dialect)
6652            new_name = mapping.get(original)
6653
6654            if new_name:
6655                table = to_table(
6656                    new_name,
6657                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
6658                )
6659                table.add_comments([original])
6660                return table
6661        return node
6662
6663    return expression.transform(_replace_tables, copy=copy)
6664
6665
6666def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6667    """Replace placeholders in an expression.
6668
6669    Args:
6670        expression: expression node to be transformed and replaced.
6671        args: positional names that will substitute unnamed placeholders in the given order.
6672        kwargs: keyword arguments that will substitute named placeholders.
6673
6674    Examples:
6675        >>> from sqlglot import exp, parse_one
6676        >>> replace_placeholders(
6677        ...     parse_one("select * from :tbl where ? = ?"),
6678        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6679        ... ).sql()
6680        "SELECT * FROM foo WHERE str_col = 'b'"
6681
6682    Returns:
6683        The mapped expression.
6684    """
6685
6686    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6687        if isinstance(node, Placeholder):
6688            if node.name:
6689                new_name = kwargs.get(node.name)
6690                if new_name:
6691                    return convert(new_name)
6692            else:
6693                try:
6694                    return convert(next(args))
6695                except StopIteration:
6696                    pass
6697        return node
6698
6699    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6700
6701
6702def expand(
6703    expression: Expression,
6704    sources: t.Dict[str, Subqueryable],
6705    dialect: DialectType = None,
6706    copy: bool = True,
6707) -> Expression:
6708    """Transforms an expression by expanding all referenced sources into subqueries.
6709
6710    Examples:
6711        >>> from sqlglot import parse_one
6712        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6713        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6714
6715        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6716        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6717
6718    Args:
6719        expression: The expression to expand.
6720        sources: A dictionary of name to Subqueryables.
6721        dialect: The dialect of the sources dict.
6722        copy: Whether or not to copy the expression during transformation. Defaults to True.
6723
6724    Returns:
6725        The transformed expression.
6726    """
6727    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
6728
6729    def _expand(node: Expression):
6730        if isinstance(node, Table):
6731            name = normalize_table_name(node, dialect=dialect)
6732            source = sources.get(name)
6733            if source:
6734                subquery = source.subquery(node.alias or name)
6735                subquery.comments = [f"source: {name}"]
6736                return subquery.transform(_expand, copy=False)
6737        return node
6738
6739    return expression.transform(_expand, copy=copy)
6740
6741
6742def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
6743    """
6744    Returns a Func expression.
6745
6746    Examples:
6747        >>> func("abs", 5).sql()
6748        'ABS(5)'
6749
6750        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6751        'CAST(5 AS DOUBLE)'
6752
6753    Args:
6754        name: the name of the function to build.
6755        args: the args used to instantiate the function of interest.
6756        copy: whether or not to copy the argument expressions.
6757        dialect: the source dialect.
6758        kwargs: the kwargs used to instantiate the function of interest.
6759
6760    Note:
6761        The arguments `args` and `kwargs` are mutually exclusive.
6762
6763    Returns:
6764        An instance of the function of interest, or an anonymous function, if `name` doesn't
6765        correspond to an existing `sqlglot.expressions.Func` class.
6766    """
6767    if args and kwargs:
6768        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6769
6770    from sqlglot.dialects.dialect import Dialect
6771
6772    dialect = Dialect.get_or_raise(dialect)
6773
6774    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
6775    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
6776
6777    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
6778    if constructor:
6779        if converted:
6780            if "dialect" in constructor.__code__.co_varnames:
6781                function = constructor(converted, dialect=dialect)
6782            else:
6783                function = constructor(converted)
6784        elif constructor.__name__ == "from_arg_list":
6785            function = constructor.__self__(**kwargs)  # type: ignore
6786        else:
6787            constructor = FUNCTION_BY_NAME.get(name.upper())
6788            if constructor:
6789                function = constructor(**kwargs)
6790            else:
6791                raise ValueError(
6792                    f"Unable to convert '{name}' into a Func. Either manually construct "
6793                    "the Func expression of interest or parse the function call."
6794                )
6795    else:
6796        kwargs = kwargs or {"expressions": converted}
6797        function = Anonymous(this=name, **kwargs)
6798
6799    for error_message in function.error_messages(converted):
6800        raise ValueError(error_message)
6801
6802    return function
6803
6804
6805def case(
6806    expression: t.Optional[ExpOrStr] = None,
6807    **opts,
6808) -> Case:
6809    """
6810    Initialize a CASE statement.
6811
6812    Example:
6813        case().when("a = 1", "foo").else_("bar")
6814
6815    Args:
6816        expression: Optionally, the input expression (not all dialects support this)
6817        **opts: Extra keyword arguments for parsing `expression`
6818    """
6819    if expression is not None:
6820        this = maybe_parse(expression, **opts)
6821    else:
6822        this = None
6823    return Case(this=this, ifs=[])
6824
6825
6826def cast_unless(
6827    expression: ExpOrStr,
6828    to: DATA_TYPE,
6829    *types: DATA_TYPE,
6830    **opts: t.Any,
6831) -> Expression | Cast:
6832    """
6833    Cast an expression to a data type unless it is a specified type.
6834
6835    Args:
6836        expression: The expression to cast.
6837        to: The data type to cast to.
6838        **types: The types to exclude from casting.
6839        **opts: Extra keyword arguments for parsing `expression`
6840    """
6841    expr = maybe_parse(expression, **opts)
6842    if expr.is_type(*types):
6843        return expr
6844    return cast(expr, to, **opts)
6845
6846
6847def true() -> Boolean:
6848    """
6849    Returns a true Boolean expression.
6850    """
6851    return Boolean(this=True)
6852
6853
6854def false() -> Boolean:
6855    """
6856    Returns a false Boolean expression.
6857    """
6858    return Boolean(this=False)
6859
6860
6861def null() -> Null:
6862    """
6863    Returns a Null expression.
6864    """
6865    return Null()
6866
6867
6868# TODO: deprecate this
6869TRUE = Boolean(this=True)
6870FALSE = Boolean(this=False)
6871NULL = Null()